-1

I am very new to SQL query so, sorry If I miss basic details. Here is the scenario:

I have to search the user email Id inside the column.

the column contains values like 

abc@company.com;xyz@company.com;test@company.com;

Now I got the user email id from query:

DECLARE @emailid AS VARCHAR(50)
SELECT @emailid = emailId FROM dbo.tblUser WHERE userName  = 'abc' AND projectId ='P1456'

I also replaced the semicolon with a comma like:

COLUMNNAME IN (REPLACE(@emailid,';' ,','))

Now I got the string like abc@company.com,xyz@company.com,test@company.com,

But IN clause treat it as a single string, So how to append inverted commas in each email ids like below to help IN clause to search?

'abc@company.com','xyz@company.com','test@company.com',
Fmanin
  • 519
  • 1
  • 12
  • 25
Rishi Jagati
  • 626
  • 1
  • 6
  • 28
  • Which dbms are you using? (That code is product specific.) – jarlh Mar 01 '18 at 08:40
  • You dont need to append anything, you would wanna search for a particular email from the string right – Ven Mar 01 '18 at 08:41
  • 3
    Are you really storing multiple email values in a singe column as a CSV? Not really a good design. And you now know why... – VDWWD Mar 01 '18 at 08:41
  • Read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** – Zohar Peled Mar 01 '18 at 10:52

1 Answers1

0

Try this:

DECLARE @emailid AS VARCHAR(50)
SELECT @emailid = 'abc@company.com;xyz@company.com;test@company.com;'

SELECT   @emailid AS emailid
        ,REPLACE(SUBSTRING( ''''+@emailid+'''', 
        PATINDEX(';',@emailid), 51),';',''+ ''''+','+''''+'') AS [replaced]

I hope you are using MSSQL

Fmanin
  • 519
  • 1
  • 12
  • 25