Login    Forum    Search    FAQ   Blog

Board index » Server Side Development » SQL




 Page 1 of 1 [ 5 posts ] 



Author Message
 Post subject: Sub strin
 Post Posted: Thu Nov 19, 2009 6:47 am 
Offline

Joined: Sat Feb 21, 2009 1:05 pm
Posts: 66
I m havin a text field nd I need to get da data in da field aftr
a specific char

For ex da column contains:

Code:
20070814~HCP*02*32*19.75*HEOS~LX*2~SV1*HC:97014:59*35.75*


I need to get wat really follows da chars 'HC', they would
not always be in da same pos in da field.

could you tell me if dis was possible nd what I search on 2 find more
info?

Thanks in advance ,

_________________
If your wife wants to learn to drive, don't stand in her way.


Top 
 Post subject: Re: Sub strin
 Post Posted: Thu Nov 19, 2009 6:47 am 
Offline

Joined: Sat Feb 21, 2009 11:06 am
Posts: 1794
Code:
select right(str, charindex('*HC:',str)+4,len(Str))
or maybe
select left(right(str, charindex('*HC:',str)+4,len(Str)), charindex('*',right(str, charindex('*HC:',str)+4,len(Str))-1)



Top 
 Post subject: Re: Sub strin
 Post Posted: Thu Nov 19, 2009 6:47 am 
Offline

Joined: Sat Feb 21, 2009 1:05 pm
Posts: 66
The right fun never gonna work wid a data type f da text. Any otr
suggestions ? Thanks

_________________
If your wife wants to learn to drive, don't stand in her way.


Top 
 Post subject: Re: Sub strin
 Post Posted: Thu Nov 19, 2009 6:48 am 
Offline

Joined: Sat Feb 21, 2009 11:06 am
Posts: 1794
Code:
DECLARE   @Sample TABLE (Col1 TEXT)

INSERT   @Sample
SELECT   '20070814~HCP*02*32*19.75*HEOS~LX*2~SV1*HC:97014:59*35.75*'

SELECT   Col1,
   SUBSTRING(Col1, CHARINDEX('*HC:', Col1), 8000)
FROM   @Sample


Top 
 Post subject: Re: Sub strin
 Post Posted: Thu Nov 19, 2009 6:48 am 
Offline

Joined: Sat Feb 21, 2009 1:08 pm
Posts: 549
how long was ur string? If it might be longer than 8000 chars then
u need not to use the charindex.
Code:

select substring(str, patindex('%*HC:%',str)+4,8000)
or maybe
select substring(substring(str, patindex('%*HC:%',str)+4,8000), charindex('*',substring(str, patindex('%*HC:%',str)+4,8000))-1)


Top 
Display posts from previous:  Sort by  
 
 Page 1 of 1 [ 5 posts ] 




Board index » Server Side Development » SQL


Who is online

Users browsing this forum: No registered users and 1 guest

 
 

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron