Solved

Extracting Data from varchard field

Posted on 2011-09-22
16
280 Views
Last Modified: 2012-06-27

I am tring to query a field which is of datatype varchar(6000), database sql 2008 R2

Examples of data in the field are

Summary of answers  (click to view all)    Q: Please select your client  A: Billy Smith    Q: Enter date for Billy Smith  A: 09/12/2009 00:00:00    Q: Select who will store the Task(s)  A: Customer  
Summary of answers  (click to view all)    Q: Please select your client  A: Mark Brown    Q: Enter date for Matt Brown  A: 09/12/2009 00:00:00    Q: Select who will store the Task(s)  A: Till safe
Summary of answers  (click to view all)    Q: Please select your client  A: Matt Baker    Q: Enter date for Matt Baker  A: 08/10/2009 00:00:00    Q: Select who will store the Task(s)  A: Unknown      

I trying to extract then answer to Q: Select who will store the Task(s) from the field

So for the above examples this would be
A: Customer
A: Till Sale
A: Unknown  

Any help would be cool
0
Comment
Question by:halifaxman
  • 10
  • 6
16 Comments
 
LVL 2

Expert Comment

by:smu95rp
ID: 36579341
Would

Select rtrim(substring(<field>, patindex('%Q: Select who will store the Task(s)  A: %',<field>) +41, 6000))
 
(replace <field> with the name of the field or variable)

do what you want?

Bascically, get whatever comes after the string 'Q: Select who will store the Task(s)  A: ' till the end of the field, trimming whitespace from the right.
0
 

Author Comment

by:halifaxman
ID: 36579510
Thanks for the answer

At the moment it brings back the below based on the examples above

Q: Q: Please select your client  A: Billy Smith    Q: Enter date for Billy Smith  A: 09/12/2009 00:00:00    Q: Select who will store the Task(s)  A: Customer
Q: Please select your client  A: Mark Brown    Q: Enter date for Matt Brown  A: 09/12/2009 00:00:00    Q: Select who will store the Task(s)  A: Till safe
Q: Please select your client  A: Matt Baker    Q: Enter date for Matt Baker  A: 08/10/2009 00:00:00    Q: Select who will store the Task(s)  A: Unknown  
0
 
LVL 2

Expert Comment

by:smu95rp
ID: 36579522
I must have understood the content of your field. The below query returns:
Customer
Till safe
Unknown
Declare @table table (string varchar(6000))
insert @table
select'Summary of answers  (click to view all)    Q: Please select your client  A: Billy Smith    Q: Enter date for Billy Smith  A: 09/12/2009 00:00:00    Q: Select who will store the Task(s)  A: Customer  '
union
select 'Summary of answers  (click to view all)    Q: Please select your client  A: Mark Brown    Q: Enter date for Matt Brown  A: 09/12/2009 00:00:00    Q: Select who will store the Task(s)  A: Till safe '
union
select 'Summary of answers  (click to view all)    Q: Please select your client  A: Matt Baker    Q: Enter date for Matt Baker  A: 08/10/2009 00:00:00    Q: Select who will store the Task(s)  A: Unknown      '

Select rtrim(substring(string, patindex('%Q: Select who will store the Task(s)  A: %', string) +41, 6000))
From @table

Open in new window

0
 
LVL 2

Expert Comment

by:smu95rp
ID: 36579548
I meant to say MISunderstood.
0
 

Author Comment

by:halifaxman
ID: 36579550
Thanks for helping

Is the answer above the same as the answer before as I am still getting the same answer as before

rtrim(substring(string, patindex('%Q: Select who will store the Task(s)  A: %', string) +41, 6000))
rtrim(substring(<field>, patindex('%Q: Select who will store the Task(s)  A: %',<field>) +41, 6000))
0
 
LVL 2

Expert Comment

by:smu95rp
ID: 36579554
Yes it's the same.
0
 
LVL 2

Expert Comment

by:smu95rp
ID: 36579575
I'm guessing the patindex isn't matching the pattern and therefore returning 0: the start of the field.
Can you confirm what results you get if you use the whole of my last query?
0
 

Author Comment

by:halifaxman
ID: 36579581
The query pulls back, example below, this the same for all row, there is a space before the Q in each row

 Q: Please select your client  A: Mark Brown    Q: Enter date for Matt Brown  A: 09/12/2009 00:00:00    Q: Select who will store the Task(s)  A: Till safe
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 2

Expert Comment

by:smu95rp
ID: 36579603
OK if the response you want is always the last answer then use this instead:

Select rtrim(reverse(left(reverse(string), patindex('%:A%',reverse(string)) -2 )))

Basically it reverses the string and grabs everything up to the, now, first ':A' pattern and reverses it again.
0
 
LVL 2

Expert Comment

by:smu95rp
ID: 36579623
D'oh or even simpler:

ltrim(right(string, patindex('%:A%',reverse(string)) -2 ))
0
 

Author Comment

by:halifaxman
ID: 36579665
Thanks for this.

This works 90% of the time

Apologies this is my fault and has only been added in the last couple of days so didnt see it.

There can aslo be data like

Summary of answers  (click to view all)    Q: Please select your client  A: Matt Baker    Q: Enter date for Matt Baker  A: 08/10/2009 00:00:00    Q: Select who will store the Task(s)  A: Unknown  Q: Select Will Storage payment method  A: Annual Paid

i.e. some field have Q: Select Will Storage payment method  A: Annual Paid added on to the end

Apologies for mssing this
0
 
LVL 2

Accepted Solution

by:
smu95rp earned 500 total points
ID: 36579843
Ok try:

rtrim(
 substring(string, charindex('A:', string, charindex('Q: Select who will store the Task', string))+ 3,

     isnull(nullif(charindex('Q:', string, charindex('A:', string, charindex('Q: Select who will store the Task', string))) -3 ,-3), len(string))

        - charindex('A:', string, charindex('Q: Select who will store the Task', string))
        )
)


In fact you can replace 'Q: Select who will store the Task'  to extract any of the values (see below code).
Declare @table table (string varchar(6000))
insert @table
select 'Summary of answers  (click to view all)    Q: Please select your client  A: Billy Smith    Q: Enter date for Billy Smith  A: 09/12/2009 00:00:00    Q: Select who will store the Task(s)  A: Customer  '
union
select 'Summary of answers  (click to view all)    Q: Please select your client  A: Mark Brown    Q: Enter date for Matt Brown  A: 09/12/2009 00:00:00    Q: Select who will store the Task(s)  A: Till safe '
union
select 'Summary of answers  (click to view all)    Q: Please select your client  A: Matt Baker    Q: Enter date for Matt Baker  A: 08/10/2009 00:00:00    Q: Select who will store the Task(s)  A: Unknown  Q: iugui    '

Select 
rtrim(
 substring(string, charindex('A:', string, charindex('Q: Please select your client', string))+ 3, 

     isnull(nullif(charindex('Q:', string, charindex('A:', string, charindex('Q: Please select your client', string))) -3 ,-3), len(string)) 

        - charindex('A:', string, charindex('Q: Please select your client', string))
        )
) [Client],

rtrim(
 substring(string, charindex('A:', string, charindex('Q: Enter date for ', string))+ 3, 

     isnull(nullif(charindex('Q:', string, charindex('A:', string, charindex('Q: Enter date for ', string))) -3 ,-3), len(string)) 

        - charindex('A:', string, charindex('Q: Enter date for ', string))
        )
) [Date],

rtrim(
 substring(string, charindex('A:', string, charindex('Q: Select who will store the Task', string))+ 3, 

     isnull(nullif(charindex('Q:', string, charindex('A:', string, charindex('Q: Select who will store the Task', string))) -3 ,-3), len(string)) 

        - charindex('A:', string, charindex('Q: Select who will store the Task', string))
        )
) [TaskStoreBy]

From @table

Open in new window

0
 

Author Comment

by:halifaxman
ID: 36579880
Brilliant Thanks Worked a treat, thanks for your help on this much appreciated
0
 
LVL 2

Expert Comment

by:smu95rp
ID: 36579884
You can even wrap each one in a Case statement to ensure you get NULL for any missing values:


case when charindex('Q: Select Will Storage payment method', string) = 0 then null else
rtrim(
 substring(string, charindex('A:', string, charindex('Q: Select Will Storage payment method', string))+ 3,

     isnull(nullif(charindex('Q:', string, charindex('A:', string, charindex('Q: Select Will Storage payment method', string))) -3 ,-3), len(string))

        - charindex('A:', string, charindex('Q: Select Will Storage payment method', string))
        )
) end [PaymentMethod]
0
 
LVL 2

Expert Comment

by:smu95rp
ID: 36579885
You're welcome.
0
 

Author Closing Comment

by:halifaxman
ID: 36579887
Thanks for the effort on this one
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now