How to find a number from a string (1,2,3,4,5,6) and remove it ?

Question

0.00/5 (No votes)

See more:

I have a stored procedure which accepts a string of unique numbers separated by commas

Ex: 1,2,3,4,5,6,7,8,9,10

Now i want to find a number say 4 and remove it from the list.

New list should look like 1,2,3,5,6,7,8,9,10

I did it using the below method

SQL

DECLARE @str varchar(100);

SET @str=','+ '1,2,3,4,5,6,7,8,9,10,11,12';

SELECT STUFF(REPLACE(@str,',12,',''), 1, 1, '');

I am checking to do it in a better way.
Does any one have a better idea.Please feel free to reply :).

Posted 2-Dec-10 0:37am

sadanandms

Updated 2-Dec-10 1:25am

Tarun.K.S

v2

Add a Solution

Comments

Sunasara Imdadhusen 2-Dec-10 6:53am

I think this would be better (:

E.F. Nijboer 2-Dec-10 6:57am

How do you mean better? I think replace is the fastest way to do it, but what is the use of stuff about?

1 solution

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)

E.F. Nijboer · Answer 1 · 2010-12-02T01:01:00

Solution 1

You could remove the use of STUFF because it doesn't do anything, so you just keep:

SELECT REPLACE(@str,',12,','')

Good luck!

Posted 2-Dec-10 1:01am

E.F. Nijboer

Comments

sadanandms 3-Dec-10 4:32am

no STUFF() is used to replace the first occurrence character after REPLACE(@str,',12,','') result i.e
,1,2,3,5,6,7,8,9,10,11

E.F. Nijboer 4-Dec-10 8:38am

You are correct :-)
An alternative could be: SELECT SUBSTRING(REPLACE(@str,',12,',''), 2, 100);
but I don't actually know if that would be any better/faster. Another method could be to separate all values and process them one-by-one, but that would definitely not be any faster.
http://www.vamsipavan.com/blog/how-to-pass-a-list-of-values-or-array-to-sql-server-stored-procedure/