Mark Wilson
asked on
Extracting Data from varchard field
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
ASKER
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
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
I must have understood the content of your field. The below query returns:
Customer
Till safe
Unknown
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
I meant to say MISunderstood.
ASKER
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))
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))
Yes it's the same.
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?
Can you confirm what results you get if you use the whole of my last query?
ASKER
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
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
OK if the response you want is always the last answer then use this instead:
Select rtrim(reverse(left(reverse (string), patindex('%:A%',reverse(st ring)) -2 )))
Basically it reverses the string and grabs everything up to the, now, first ':A' pattern and reverses it again.
Select rtrim(reverse(left(reverse
Basically it reverses the string and grabs everything up to the, now, first ':A' pattern and reverses it again.
D'oh or even simpler:
ltrim(right(string, patindex('%:A%',reverse(st ring)) -2 ))
ltrim(right(string, patindex('%:A%',reverse(st
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Brilliant Thanks Worked a treat, thanks for your help on this much appreciated
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]
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
- charindex('A:', string, charindex('Q: Select Will Storage payment method', string))
)
) end [PaymentMethod]
You're welcome.
ASKER
Thanks for the effort on this one
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.