Re: Return Part of a text field

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

  • Re: Establishing Precedence In ORDERBY Condition Causing Problems.
    ... inner joins - so you should either modify the query to use inner join ... Hugo Kornelis, SQL Server MVP ... WHEN 1 THEN registerDate ... from PersonalPhotos withunion select distinct email_address ...
    (comp.databases.ms-sqlserver)
  • Re: Help with missing data in query
    ... I have a table with Part, MonthSold, ItemsSold ... i need to generate a view comparing this years sales to lastyears sales and ... UNION ALL ... Hugo Kornelis, SQL Server MVP ...
    (microsoft.public.sqlserver.mseq)