Select 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

SQL Server 2005 Forums

Transact-SQL (2005)

Select Substring

Author

Topic

kneekill
Yak Posting Veteran

76 Posts

Posted - 2010-07-02 : 07:19:07

Hi All

I have a column where i need to select a substring form each row . The substring is a word which start with letters 'sp' and the word can repeat only once per row or not at all.There is no fixed position for the word to appear in the sentence.Kindly guide me on how to achieve this

Example:

create table #TempString (testString varchar(50))

insert into #TempString (testString)
select 'exec sptest qwert' union all
select 'sptest1 xxxx' union all
select 'asasa' union all
select 'Declare yyyyy sptest2 '

select * from #TempString

My Final result should be

sptest
sptest1
sptest2

Sachin.Nand

2937 Posts

Posted - 2010-07-02 : 07:39:58

[code]
select substring(initialstring,0,charindex(' ',initialstring,1)) from
(
select *,substring(testString,charindex('sp',testString,1),len(testString))
as initialstring from #TempString
)t
[/code]

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

PBUH

kneekill
Yak Posting Veteran

76 Posts

Posted - 2010-07-02 : 07:45:39

Thanks a lot Idera. It works perfectly

quote:
Originally posted by Idera

select substring(initialstring,0,charindex(' ',initialstring,1)) from
(
select *,substring(testString,charindex('sp',testString,1),len(testString))
as initialstring from #TempString
)t

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

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-07-02 : 07:48:06

Welcome.

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-02 : 08:23:34

Or
select
testString,
left(substring(testString+' ',patindex('%sp_%',testString+' '), len(testString+' ')),charindex(' ',substring(testString+' ',patindex('%sp_%',testString+' '), len(testString+' ')))) as x
from #TempString
where testString like '%sp_%'

But it is very similar to Idera's solution