Help with text pls. - 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)

Help with text pls.

Author

Topic

skysyb
Starting Member

16 Posts

Posted - 2008-08-08 : 07:04:35

Hi, i've a text columns which has the messages as follows.

Amend succesfully in database with 800.0 contracts [Order Id]:=O123456 [Ticket Refer]:=123456789 [Apend]:=123456 <some junk test>

However, I only need , [Ticket Refer]:=123456789 [Apend]:=123456 out of that message in my query. Can you please help me how can i go about doing that ?

Thanks in advance.

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-08 : 07:09:36

Look up the substring command

skysyb
Starting Member

16 Posts

Posted - 2008-08-08 : 15:23:06

sorry, i probably should have been submitted too fast. Infact my question here is :

how do I go a bout taking only the 'Ticket Refr=123456789' and 'Apend=123456' parts out the text message w/o square brackets[]. Also, these two references are random in the text message. they can be anywhere in the huge text. but one thing common is , ticket ref & apend are always next to each other.

any help would be really appreciated.

thanks again.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-09 : 00:02:31

[code]DECLARE @STR varchar(100),
@result varchar(100),
@search varchar(30),
@idx int

SELECT @STR = 'Amend succesfully IN database WITH 800.0 contracts [Order Id]:=O123456 [Ticket Refer]:=123456789 [Apend]:=123456 <some junk test>'

SELECT @result = NULL

SELECT @search = '[Order Id]'
SELECT @idx = CHARINDEX(@search, @STR)
SELECT @result = ISNULL(@result + ' ', '') +
CASE WHEN @idx <> 0
THEN SUBSTRING(@STR, @idx, CHARINDEX(' ', @STR, CHARINDEX(@search, @STR) + LEN(@search)) - @idx)
END

SELECT @search = '[Ticket Refer]'
SELECT @idx = CHARINDEX(@search, @STR)
SELECT @result = ISNULL(@result + ' ', '') +
CASE WHEN @idx <> 0
THEN SUBSTRING(@STR, @idx, CHARINDEX(' ', @STR, CHARINDEX(@search, @STR) + LEN(@search)) - @idx)
END
SELECT @result = REPLACE(REPLACE(@result, '[', ''), ']', '')

SELECT @result

[/code]

KH
[spoiler]Time is always against us[/spoiler]

skysyb
Starting Member

16 Posts

Posted - 2008-08-15 : 14:03:47

Wonderful. Thanks Much for your help Khtan. (sorry, I was away and didn't get a chance to look at your response as of today).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources