1

I'm trying to get the domain instead of the full e-mail address in my query for Domain_. In the statement below I select user_data. Email_addressbut that provides foo@bar.com instead of bar.com which isn't sufficient:

INSERT INTO user_orders
        (`User_ID`, `Order_Number`, `Customer_ID`, `Email_address`, `Domain_`)
SELECT 
       user_data.`User_ID`, order_data.`Order_Number`, user_data.`Customer_ID`, 
       user_data.`Email_address`, user_data.`Domain_` 
FROM user_data
INNER JOIN order_data ON order_data.User_ID = user_data.User_ID;

My understanding was SUBSTRING_INDEX(Email_address,'@',-1) as Domain_ would work so I tried:

INSERT INTO user_orders
         (`User_ID`, `Order_Number`, `Customer_ID`, `Email_address`, `Domain_`)
SELECT 
     user_data.`User_ID`, order_data.`Order_Number`, user_data.`Customer_ID`, user_data.`Email_address`,  
    (SUBSTRING_INDEX(SUBSTR(user_data.`Email_address`, INSTR(user_data.`Email_address`, '@') + 1),'.',1)) as user_data.`Domain_` 
FROM user_data
INNER JOIN order_data ON order_data.User_ID = user_data.User_ID;

but the query fails and provides a syntax error.

To help isolate it more for clarity this is what I'm doing differently:

(SUBSTRING_INDEX(SUBSTR(user_data.`Email_address`, INSTR(user_data.`Email_address`, '@') + 1),'.',1)) as user_data.`Domain_`

Any help would be appreciated as I'm stuck.

Error: INSERT INTO user_orders(User_ID, Order_Number, Customer_ID, Email_address, Domain_) select user_data.User_ID, order_data.Order_Number, user_data.Customer_ID, user_data.Email_address, (SUBSTRING_INDEX(SUBSTR(user_data.Email_address, INSTR(user_data.Email_address, '@') + 1),'.',1)) as user_data.Domain_ FROM user_data INNER JOIN order_data on order_data.User_ID = user_data.User_ID; /* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.Domain_ FROM user_data INNER JOIN order_data on order_data.User_ID = user...' at line 2 */```

FanoFN
  • 6,815
  • 2
  • 13
  • 33
denise1223
  • 11
  • 2
  • 1
    Tip: Focus on the domain problem first. This query has a *lot* more going on than that. Also please include the *exact* error you get. – tadman Mar 08 '21 at 00:25
  • It only gives me a 1064 error. – denise1223 Mar 08 '21 at 00:26
  • Again, *exact error*, the whole thing, not just some number. The numbers are not especially unique. – tadman Mar 08 '21 at 00:27
  • 2
    When battling syntax errors, delete stuff from your query until it works, then add back in incrementally until you get an error. This will give you a much smaller change to focus on. – tadman Mar 08 '21 at 00:28
  • updated the error – denise1223 Mar 08 '21 at 00:29
  • user_data.(SUBSTRING_INDEX(SUBSTR(Email_address, INSTR(Email_address, '@') + 1),'.',1)) yields an error but user_data.`Email_address` doesn't – denise1223 Mar 08 '21 at 00:40
  • You can't do stuff like `user_data.(...)`, that isn't a column. Can you rework this to do the simplest possible `SELECT` statement on your schema that does *only* `Email_address, domain` where `domain` is your troublesome `SUBSTR(...)` thing? – tadman Mar 08 '21 at 00:41
  • So how do I truncate the Email_address to just the domain in that select statement then? – denise1223 Mar 08 '21 at 00:42
  • Does [this answer](https://stackoverflow.com/questions/2628138/how-to-select-domain-name-from-email-address) work? – tadman Mar 08 '21 at 00:43
  • I still can't figure it out but I'll just move on thanks anyway – denise1223 Mar 08 '21 at 00:45
  • The last statement after your `SUBSTRING_INDEX()` function is the culprit. You don't need `as user_data.Domain_`. You just have to change that to something like `as 'Domain_'`, with apostrophe (single quote) wrapping it instead of `backquotes`. Actually, in my opinion, you don't even need to assign alias `as ....` since this is an `INSERT` syntax. – FanoFN Mar 08 '21 at 01:07
  • By the way, were you going to return `bar.com` or just `bar` from ` foo@bar.com` ? Because your current function `SUBSTRING_INDEX(SUBSTR(INSTR()))` is returning `bar` only. – FanoFN Mar 08 '21 at 01:11

0 Answers0