Find a string in 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 2008 Forums

Transact-SQL (2008)

Find a string in field

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2011-01-28 : 08:36:26

Hi,

I have the following text in a field 'File_2010Apr_'

How would I be able to return the text after the first _ and before the 2nd _?

Thanks in advance.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-28 : 08:41:49

select substring(fld, charindex('_',fld)+1, charindex(fld,'_',charindex('_',fld)+1)-charindex('_',fld)-1)
from tbl

Probably not quite correct but you'll get the idea.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2011-01-28 : 08:56:20

im abit confused

the text in the field is ip_2010Apr_file

Im running the following charindex(fld, '_', 1)

and its returning no value, does charindex only work with one field type??

quote:
Originally posted by nigelrivett

select substring(fld, charindex('_',fld)+1, charindex(fld,'_',charindex('_',fld)+1)-charindex('_',fld)-1)
from tbl

Probably not quite correct but you'll get the idea.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-28 : 09:02:30

Sorry - typed it the wrong way round in one of them

select substring(fld, charindex('_',fld)+1, charindex('_',fld,charindex('_',fld)+1)-charindex('_',fld)-1)
from tbl

syntax is select charindex('_','ASDA_ASDF')

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2011-01-28 : 09:37:05

Ok thanks for that :)

Have one more request which might be challenging.

If the text in the field is 'file_is_not_this'

How do I retrieve the text after the 3rd underscore? '_'?

Thanks!

quote:
Originally posted by nigelrivett

Sorry - typed it the wrong way round in one of them

select substring(fld, charindex('_',fld)+1, charindex('_',fld,charindex('_',fld)+1)-charindex('_',fld)-1)
from tbl

syntax is select charindex('_','ASDA_ASDF')

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2011-01-28 : 09:45:02

or even the last underscore in the text?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-28 : 09:51:50

If it's after the last one then
select substring(fld, len(fld) - charindex('_',reverse(fld)),len(fld))
from tbl

in general
;with cte as
(
select fld, strtloc = 1, endloc = charindex('_',fld), seq = 1 from tbl
union all
select fld, strtloc = endloc+1, endloc = charindex('_',fld, endloc+1), seq = seq+1 from cte where endloc <> 0
)
select seq, fld, substring(fld, strtloc, case when endloc = 0 then len(fld)+1 else endloc end - strtloc - 1
from cte

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-28 : 09:52:05

quote:
Originally posted by cipriani1984

Ok thanks for that :)

Have one more request which might be challenging.

If the text in the field is 'file_is_not_this'

How do I retrieve the text after the 3rd underscore? '_'?

Thanks!

quote:
Originally posted by nigelrivett

Sorry - typed it the wrong way round in one of them

select substring(fld, charindex('_',fld)+1, charindex('_',fld,charindex('_',fld)+1)-charindex('_',fld)-1)
from tbl

syntax is select charindex('_','ASDA_ASDF')

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Use split function and search for the same

Madhivanan

Failing to plan is Planning to fail

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2011-01-28 : 09:56:02

when I used the following you supplied

select substring(fld, len(fld) - charindex('_',reverse(fld)),len(fld))
from tbl

it returned the t and _ before the text after the last underscore

'r_this'

How Would I split and search then?

quote:
Originally posted by nigelrivett

If it's after the last one then
select substring(fld, len(fld) - charindex('_',reverse(fld)),len(fld))
from tbl

in general
;with cte as
(
select fld, strtloc = 1, endloc = charindex('_',fld), seq = 1 from tbl
union all
select fld, strtloc = endloc+1, endloc = charindex('_',fld, endloc+1), seq = seq+1 from cte where endloc <> 0
)
select seq, fld, substring(fld, strtloc, case when endloc = 0 then len(fld)+1 else endloc end - strtloc - 1
from cte

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-28 : 10:19:26

select substring(fld, len(fld) - charindex('_',reverse(fld)) + 2,len(fld))
from tbl

The cte shows how to split into component strings.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources