select query using substring to check if comma

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)

select query using substring to check if comma

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-07-27 : 11:30:55

Is it possible to use substring to check if a column's data has comma string in it.

select sfilenames from tablefiles where substring(sfilenames,',')

Thank you very much for the helpful info.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-27 : 11:40:42

[code]declare @t table (sfilenames varchar(20))
insert @t
select 'test,'
union all select 'test'
union all select 'tes,t'
union all select ',test'

select * from @t where charindex(',',sfilenames) > 0[/code]

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-27 : 11:40:43

select sfilenames from tablefiles where charindex(',',sfilenames ) > 0

Sachin.Nand

2937 Posts

Posted - 2010-07-27 : 11:47:23

Wow difference of juest 1 sec between vijay & bohra's replies.You cannot get this close on sqlteam.
anyways here is my shot at this one.


select * from table where len(column)-len(REPLACE(column,',',''))>0

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Kristen
Test

22859 Posts

Posted - 2010-07-27 : 12:15:41

[code]select sfilenames
from tablefiles
where sfilenames LIKE '%,%'
[/code]
or am I missing something?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-28 : 06:37:23

quote:
Originally posted by Kristen

select sfilenames
from tablefiles
where sfilenames LIKE '%,%'

or am I missing something?

No. All others are missing

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2010-07-28 : 07:21:53

quote:
Originally posted by madhivanan

quote:
Originally posted by Kristen

select sfilenames
from tablefiles
where sfilenames LIKE '%,%'

or am I missing something?

No. All others are missing

Madhivanan

Failing to plan is Planning to fail

Why???

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-28 : 07:26:21

quote:
Originally posted by Idera

Wow difference of juest 1 sec between vijay & bohra's replies.You cannot get this close on sqlteam.
anyways here is my shot at this one.

select * from table where len(column)-len(REPLACE(column,',',''))>0

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

I think this isn't ok.
len('ab') - len(replace('ab',',','')) is also > 0

No, you're never too old to Yak'n'Roll if you're too young to die.

Sachin.Nand

2937 Posts

Posted - 2010-07-28 : 07:45:21

len('ab') - len(replace('ab',',','')) is also > 0

No its not


declare @t table (sfilenames  varchar(20))
insert @t
select 'test,'
union all select 'test'
union all select 'tes,t'
union all select ',test'
union all select 'ab'

select * from @t where len(sfilenames)-len(replace(sfilenames,',',''))>0

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-28 : 07:48:30

Oh sorry

I was blind...

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-28 : 08:30:04

quote:
Originally posted by Idera

quote:
Originally posted by madhivanan

quote:
Originally posted by Kristen

select sfilenames
from tablefiles
where sfilenames LIKE '%,%'

or am I missing something?

No. All others are missing

Madhivanan

Failing to plan is Planning to fail

Why???

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

You all missed the simplest method that Kristen posted

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources