Re: Return Part of a text field

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

Relevant Pages

  • Return Part of a text field
    ... I have a table with a field named "swNotes". ... The field type is ntext. ... field can contain a variety of data. ...
    (microsoft.public.sqlserver.mseq)
  • Errors with Union Operator
    ... In attempting to run a relatively complex union statement I came across the ... 'The ntext and image data types cannot be used in WHERE,HAVING,GROUP By,ON, ... select ntextcol from tbl1 union ...
    (microsoft.public.sqlserver.ce)
  • Union Query Problems
    ... I have two querys that work fine so I joined them with a Union and I get ... The text, ntext, or image data type cannot be selected as DISTINCT. ... an equal number of expressions in their target lists ...
    (microsoft.public.sqlserver.programming)
  • Re: Comparing two "identical" tables
    ... Yep, text, ntext, and image. ... I don't know what sort of fields ... Scott's database has. ... > using DISTINCT when you try to UNION as opposed to UNION ALL. ...
    (microsoft.public.sqlserver.programming)