MS SQL Server MSEQ - Return Part of a text field

Author

Return Part of a text field

tdom

2005-06-23, 11:23 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?

Thanks.

Hugo Kornelis

2005-06-30, 7:24 am

On Thu, 23 Jun 2005 09:10:01 -0700, tdom wrote:

>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?
>
>Thanks.

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
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

tdom

2005-06-30, 11:23 am

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

"Hugo Kornelis" wrote:

> On Thu, 23 Jun 2005 09:10:01 -0700, tdom wrote:
>
>
> 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
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Sponsored Links