Post reply

Substring / Patindex for wildcard select

  • January 17, 2014 at 9:10 am

    #281260

    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

  • January 17, 2014 at 9:14 am

    #1681123

    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.

  • January 17, 2014 at 9:17 am

    #1681126

    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?!

  • January 17, 2014 at 9:19 am

    #1681128

    Because there is nothing that tells it not to 🙂

  • January 17, 2014 at 9:27 am

    #1681131

    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

  • January 17, 2014 at 9:39 am

    #1681138

    Thanks for the replies - I get it now!

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

    Cheers for the help 😀

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply