How to replace string in sqlserver with single quotes

Question

1.80/5 (2 votes)

See more:

i have values Employee has

Declare emp varchar(10)
set emp ='harsh,suresh, mohan'

select * form Emp where name in (emp)

What I have tried:

Declare emp varchar(10)
set emp ='harsh,suresh, mohan'

select * form Emp where name in (replace(emp, ',','',''))

Posted 22-Jul-18 21:58pm

Member 12640353

Updated 24-Jul-18 18:53pm

Add a Solution

Comments

Naga Sindhura 25-Jul-18 9:48am

How data in name field stored in?
for 'harsh,suresh, mohan' condition - three different records- one for harsh, other for suresh, And, one more for mohan.
/Or harshsureshmohan /or something else
Please post your comment?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-07-22T22:18:00

Solution 1

You can't do it directly. See here: Using comma separated value parameter strings in SQL IN clauses[^]

Posted 22-Jul-18 22:18pm

OriginalGriff

aparnalakshmi · Answer 2 · 2018-07-22T22:24:00

Solution 2

declare @emp VARCHAR(10)
set @emp= 'harsh,suresh,mohan'
select @emp
SELECT REPLACE(@emp, ',','','')

Posted 22-Jul-18 22:24pm

aparnalakshmi

Updated 22-Jul-18 22:25pm

v2

Comments

CHill60 25-Jul-18 9:14am

This generates a syntax error. Please check your code before posting or state that it is untested

Naga Sindhura 25-Jul-18 9:44am

may be her intention is SELECT REPLACE(@emp, ',','') not SELECT REPLACE(@emp, ',','','')

CHill60 25-Jul-18 10:38am

And then I would comment that it just removes the commas and does not replace them with single quotes as the title clearly stated. I would also then comment that a varchar(10) is not long enough for the string 'harsh,suresh,mohan' (18 characters), so my comment "Please check your code before posting or state that is is untested" still stands

Naga Sindhura 26-Jul-18 7:29am

Agreed.. But small correction ...varchar(10) mentioned in the Problem Statement itself.. I know it is wrong as you mention earlier..

Member 11621026 · Answer 3 · 2018-07-24T18:54:00

Solution 3

declare @emp VARCHAR(100)
set @emp= 'harsh,suresh,mohan'
select @emp
SELECT REPLACE(@emp, ',',' ')

Posted 24-Jul-18 18:54pm

Member 11621026

Comments

CHill60 25-Jul-18 9:15am

This replaces the commas with a space. The OP wanted to replace them with single quotes - as the title clearly states