Find occurance of substring - 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

General SQL Server Forums

New to SQL Server Programming

Find occurance of substring

Author

Topic

razeena
Yak Posting Veteran

54 Posts

Posted - 2011-07-22 : 01:32:27

Consider a table with academic information

Declare @TestTable table(strname varchar(20))
insert into @testtable
select 'some board some' union all
select 'some secondary blah' union all
select 'some university' union all
select 'schools of something'

If the column contains value either 'board' or 'secondary' or 'schools' anywhere in the name , I need return
value as precollege ,otherwise college.

Name Category
******** *************
some board some precollege
some secondary blah precollege
some university college
schools of something precollege

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-22 : 02:02:32

select *, case when strname like '% board #' then 'precollege'
when strname like '% secondary #' then 'precollege'
when strname like '% schools #' then 'precollege'
else 'college'
end
from dbo.table1

N 56°04'39.26"
E 12°55'05.63"

razeena
Yak Posting Veteran

54 Posts

Posted - 2011-07-22 : 06:21:39

Thanks. I am putting the scenario in the other way.

If there is board/schools/secondary keyword in the string, need as precollege
In other cases if there is a slash present, cut the data before the slash
If both the above doesn't match,get the string as it is.
For eg,like below data.

***************************************
some board some------precollege
some secondary blah-------Precollege
masters/mba----- masters
academy ---academy
schools of something--------precollege

Girls/Board ------------------Precollege
**********************************************

I tried the code below, some error is obtained
Declare @TestTable table(strname varchar(20))
insert into @testtable
select 'some board some' union all
select 'some secondary blah' union all
select 'masters/mba' union all
select 'academy ' union all
select 'schools of something' union all
select 'Girls/Board'

select *,
case
when strname like '%board%' then 'precollege'
when strname like '%secondary%' then 'precollege'
when strname like '%schools%' then 'precollege'
WHEN CHARINDEX('/',strname) >0 THEN LEFT(strname,CHARINDEX('/',strname))-1
else LEN(strname)
end AS Category
from @testtable

quote:
Originally posted by SwePeso

select *, case when strname like '% board #' then 'precollege'
when strname like '% secondary #' then 'precollege'
when strname like '% schools #' then 'precollege'
else 'college'
end
from dbo.table1

N 56°04'39.26"
E 12°55'05.63"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-22 : 06:46:36

select *,
case
when strname like '%board%' then 'precollege'
when strname like '%secondary%' then 'precollege'
when strname like '%schools%' then 'precollege'
WHEN CHARINDEX('/',strname) >0 THEN LEFT(strname,CHARINDEX('/',strname)-1)
else convert(varchar,LEN(strname))
end AS Category
from @testtable

Corey

I Has Returned!!

razeena
Yak Posting Veteran

54 Posts

Posted - 2011-07-22 : 07:28:59

Great!!

quote:
Originally posted by Seventhnight

select *,
case
when strname like '%board%' then 'precollege'
when strname like '%secondary%' then 'precollege'
when strname like '%schools%' then 'precollege'
WHEN CHARINDEX('/',strname) >0 THEN LEFT(strname,CHARINDEX('/',strname)-1)
else convert(varchar,LEN(strname))
end AS Category
from @testtable

Corey

I Has Returned!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-07-25 : 06:46:11

Also make sure to specify the length for the varchar datatype when used with CAST ro CONVERT
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources