Retrieve Part of a String (T-SQL)




--Parsename
select parsename(replace(stuff('123*adsf*wert*ponm*456',1,charindex('*','123*adsf*wert*ponm*456'),''),'*','.') ,2) val1


--Recursive CTE
create table test (s varchar(100))
insert into test values( '123*adsf*wert*ponm*456')

;with mycte as (
    select 1 as i, 0 as  b,  charindex('*', s) pos, s from test
    union all
    select i + 1, pos as b, charindex('*',s, pos + 1), s
    from mycte
    where pos > 0
)
select i,   substring(s,b+1, Case WHen pos-b-1>0 Then pos-b-1 Else 999 End ) val1
from mycte 
Where i=4

;with mycte as (
    select 1 as i, 0 as  b,  charindex('*',  '123*adsf*wert*ponm*456') pos,  '123*adsf*wert*ponm*456' s
    union all
    select i + 1, pos as b, charindex('*',s, pos + 1), s
    from mycte
    where pos > 0
)
select i,   substring(s,b+1, Case WHen pos-b-1>0 Then pos-b-1 Else 999 End ) val1
from mycte 
Where i=4

 

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s