SQL - Choose field based on another fields value

Morten Lund

Posted on 08/07/2013 10:38:45

How can I choose a field based on another fields value in SQL? In MySQL I would use CONCAT - but it only works in SQL Server 2012 - as far as I can see.

I'm trying to do the following from the AccessUser table:

SELECT Firm.*, Empl.*
FROM AccessUser Firm, AccessUser Empl
WHERE Firm.AccessUserParentID = '261' AND Empl.AccessUserGroups LIKE ('%' + Firm.AccessUserID + '%')

In my search for an solution, I've found out that I could use '+' (plusses) instead of CONCAT - But all i get is "Incorrect syntax near 'Firm'" - which relates to the 'LIKE' section of the SQL. I have tried to strip the parenthethis, the spaces etc. but nothing seems to work.

Is it not possible to do such query?! Can someone help?

Replies

Morten Lund

Posted on 08/07/2013 11:33:24

I have tried different things now - and according to the docs, this should be OK

SELECT Firm.*, Empl.*
FROM AccessUser AS Firm, AccessUser AS Empl
WHERE Firm.AccessUserParentID = '261' AND Empl.AccessUserGroups LIKE ('%@' + CAST(Firm.AccessUserID AS VARCHAR(10)) + '@%')

But it returns "Incorrect syntax near 'CAST'."

Docs: http://msdn.microsoft.com/en-us/library/ms177561%28v=sql.100%29.aspx

Morten Snedker

Posted on 08/07/2013 13:12:37

Hi Morten,

It tried copy/paste your code and to me it works fine:

So can't really figure what goes wrong. I don't take you have AccessUserIDs longer the 10 characters?

Is it hosted @ hostnordic? Then I could try there.

/Snedker

Morten Lund

Posted on 08/07/2013 13:25:54

Hi Morten,

Thank you for the answer.

Actually, until now I've tested in SQL Firehose - where the errors appeared.

But now I have tried to put it inside a DataList and everything works fine! So that's great..

Does the SQL Firehose not accept '+' operands? :)

You must be logged in to post in the forum

Developer forum

SQL - Choose field based on another fields value

Replies