Return Part of a text field -- sql server mseq

tdom
6/23/2005 9:10:01 AM

I have a table with a field named "swNotes". The field type is ntext. The
field can contain a variety of data. What I need to do is pull out just one
particular piece. Here is an example of 4 different rows of data in the
"swNotes" field:

--> Color = Green <-- --> Number = 1 <-- --> Notes = Today is Thursday. <--
--> Number = 25 <-- --> Notes = Hello <--
--> Weather = Hot <-- --> Notes = I need help. <-- --> Number = 567 <--
--> Color = Green <-- --> Notes = No number in this row. <--

My goal is to retrieve "Number = xxx".
So my returned data should be:

Number = 1
Number = 25
Number = 567
Null

Since "Number = " is not in the same position each time substring does not
work. And in some cases the "Number = xxx" is not provided.

Using Query Analyzer how can I complete this task?

tdom
6/30/2005 8:25:02 AM

That's exactly what I was looking for. Thank You! Tim

[quoted text, click to view]

Hugo Kornelis
6/30/2005 11:30:30 AM

[quoted text, click to view]

Hi tdom,

You can use SUBSTRING and POSINDEX functions. However, that will only
work if the "--> Number = ?? <--" part is somewhere in the first 4000
characters of the text.

create table test (swNotes ntext)
go
insert test
select '--> Color = Green <-- --> Number = 1 <-- --> Notes = Today is
Thursday. <--'
union all
select '--> Number = 25 <-- --> Notes = Hello <--'
union all
select '--> Weather = Hot <-- --> Notes = I need help. <-- --> Number
= 567 <--'
union all
select '--> Color = Green <-- --> Notes = No number in this row. <--'
go
SELECT CASE
WHEN swNotes LIKE '%--> Number = % <--%'
THEN SUBSTRING(swNotes,
CHARINDEX('--> Number =', swNotes) + 4,
CHARINDEX(' <--',
SUBSTRING(swNotes,
CHARINDEX('--> Number =',
swNotes) + 4,
4000)) - 1)
ELSE NULL
END
FROM test
go
drop table test
go

Best, Hugo
--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server mseq

sql server mseq : Return Part of a text field