VARCHAR comma separated field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

VARCHAR comma separated field

Author

Topic

tirthadip
Starting Member

2 Posts

Posted - 2010-09-02 : 01:50:18

I have a table whose structure is as follows:

noteid note sender receiver
1 Test1 James John,Smith
2 Test2 Rob William,Smith
3 Test3 Smith Rob,John
4 Test4 Jane Smith,Rob
receiver is a VARCHAR comma separated field

I want the noteid of those record which have SMITH as receiver i.e 1,2,4
please help

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-02 : 01:57:01

for test1, Both John and Smith are receivers?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-02 : 01:58:15

[code]select noteid from table where receiver like '%Smith%'[/code]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-02 : 04:15:35

Hi sakets.

That would also bring back names such like smithe etc.

Maybe better would be


WHERE
    [receiver] LIKE '%,Smith,%'

If you wanted only to get exactly Smith and no other characters in the name

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-02 : 04:33:52

quote:
Originally posted by Transact Charlie

Hi sakets.

That would also bring back names such like smithe etc.

Maybe better would be

WHERE
[receiver] LIKE '%,Smith,%'

If you wanted only to get exactly Smith and no other characters in the name

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Ah correct.

Charlie, What you gave mightn't work if Smith is the first in the list. [Smith,Shay,...]

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-02 : 04:35:31

this should be good I think-

WHERE
    [receiver] LIKE '%,Smith,%'
    or 
    [receiver] LIKE 'Smith,%'    or 
    [receiver] LIKE '%,Smith'

PavanKK
Starting Member

32 Posts

Posted - 2010-09-02 : 04:51:36

Small modification to work if Smith is the last in the list. [Shay,...,Smith]

WHERE CHARINDEX(',SMITH,',','+receiver+',')>0

quote:
Originally posted by sakets_2000

this should be good I think-

WHERE
[receiver] LIKE '%,Smith,%'
or
[receiver] LIKE 'Smith%'

Kristen
Test

22859 Posts

Posted - 2010-09-02 : 06:40:37

[code]WHERE
',' + [receiver] + ',' LIKE '%,Smith,%'
[/code]
will do - and, I think!!, will be faster than using a function such as CHARINDEX

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources