how to split string?

Question

hi

i have two field for example :

id          text

1          1,2

2          1,3

3          2,3

4          1,3

and i have a value for example : 2

i'll find all vales 2 in my table and remove they

please help me how to it do ?

it's urgent

thanks

Answer 1

You mean you want to replace 2 in text column..??

select replace(text,'2','') from yourtable

VT

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 2

thanks for your answer

no my mean was , i want remove from all records in column text

please help me

Answer 3

try

select case when text like '%,2' or  text like '2,%' then '' else text from yourtable

update yourtable set text=when text like '%,2' or  text like '2,%' then '' else text

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 4

thanks for your answer

i want clear value 2 from all record without select id=1 or id=2 ....

please if possible  show me with code

thanks

Answer 5

could you please post sample data and expected result

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 6

i have a table called myTable :

id          text

1          1,2

2          1,3

3          2,3

4          1,3

and i want create a sp and thet sp have a param that my param is 2

now i want that sp search in myTable and find value 2 from column text

please help me

Answer 7

On you last post you said you want remove a value from text column now you saying select ..

select *

from mytable
where text like  '%,2' or  text like '2,%' 

VT

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 8

Hi vahidbakhtiary,

Despite VT's request, you stiill have not posted the expected result,
which makes it hard to help. Here is one try, that may or may not be what yoou are looking for:

UPDATE MyTable
SET text = REPLACE(REPLACE(text, ',2', ''), '2,', '')
WHERE text LIKE '%2,%'
OR text LIKE '%,2%';

Note that the above code will not perform well, and it will fail quite
spectacularly if your the text string contains code like '12,31' or
'17,24'.

A far better solution is to rethink your design. Storing a
comma-seperated list of values in a single column violates relational
principles, and you just found out why this is a bad idea.

---

Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Answer 9

Hello,

Follow the links below. It will help you

http://blog.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx

http://www.dotnetspider.com/forum/201560-How-split-comma-separated-string-sql-server-procedure.aspx

http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/4126a010-6885-4eb0-b79c-c798c90edb85

Answer 10

The following article demonstrates UDF construction and delimited string splitting:

http://www.sqlusa.com/bestpractices/training/scripts/userdefinedfunction/

---

Kalman Toth SQL SERVER & BI TRAINING