SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Substring / Patindex for wildcard select


Substring / Patindex for wildcard select

Author
Message
gwyn.jones
gwyn.jones
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 66
Hi,

I am trying to extract a date string from a field, if the field populated has CED: before it.

So if the comment field shows CED:01/01/14 then give me 01/01/14

There could be characters prior or after the CED:xx/xx/xx

select
SUBSTRING (comment,PATINDEX('%CED:%',comment)+4,LEN(Comment)) as Date
from data

It's pulling back data even though CED: doesn't exist in the Comment field for some reason. I am getting the date in places so it's part working.

I cannot use a where as this is an add on to a current script.

Thanks for your help. This is doing my head in!!!!

Thanks,

Gwyn
batgirl
batgirl
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2123 Visits: 1820
If you want a result for every row, then use a case statement to make the result as you wish when "CED:" is present.
If you only want results for rows that do contain "CED:", then use a where clause to limit.
gwyn.jones
gwyn.jones
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 66
Thanks :-)

I will try that case. Cannot use where as script is huge as it is with lots of unions and where clauses so just wanted a nice easy add on in the select!!

Why is it giving output to fields that don't contain CED?!
batgirl
batgirl
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2123 Visits: 1820
Because there is nothing that tells it not to Smile
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40535 Visits: 19813
gwyn.jones (1/17/2014)
Thanks :-)

I will try that case. Cannot use where as script is huge as it is with lots of unions and where clauses so just wanted a nice easy add on in the select!!

Why is it giving output to fields that don't contain CED?!


Because PATINDEX('%CED:%',comment) will return 0 if the field doesn't contain CED. With the CASE statement, you can't validate to use values only when PATINDEX('%CED:%',comment) > 0


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
gwyn.jones
gwyn.jones
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 66
Thanks for the replies - I get it now!

Did the case, then a right() to get the date.

Cheers for the help :-D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search