Results 1 to 4 of 4

Thread: Query question

  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: Query question

    Hi All,

    I have a couple of strings that look like this:

    Test-Table Data Process: Test sample data - Every Sunday @ 10:30pm
    Test Maintenance Process: Delete sample from key tables (without resetting identity values) - Every Saturday @ 10:50pm
    Test-Table Data Process: Moving responsible parties - Daily @ 10:45pm

    I am trying to extract the words that are between the colon and the second occurence of the dash in the first and third strings,
    and to extract the words that are between the colon and the dash in the second string.

    I am having a hard time with it. Can anyone please help?

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    1. get the charindex of the colon ... that value + 1 is the start of the string
    2. get the charindex of the dash beginning from the value derived in step 1
    3 capture the string beginning at the value in step 1 for the length of the value in step 2 less 1 position.

    Of course this assumes that all char/varchar strings will have both ... you will need to code for the exception.

    Refer to BOL for parameters of CHARINDEX.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    How about

    Code:
    SELECT SUBSTRING(MyString, CHARINDEX(":", MyString) + 1, CHARINDEX("-", MyString, CHARINDEX(":", MyString) + 1) - CHARINDEX(":", MyString) - 1)
    This assumes the same basic format of your text strings...and my example code will leave you with a leading and trailing space, but you can get rid of those by changing the code to:
    Code:
    SELECT SUBSTRING(MyString, CHARINDEX(': ', MyString) + 2, CHARINDEX(' -', MyString, CHARINDEX(': ', MyString) + 2) - CHARINDEX(':', MyString) - 2)
    Again, remember that this assumes that all your source strings are formatted as you suggest.
    Last edited by TallCowboy0614; 05-29-07 at 16:07.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Jul 2004
    Posts
    268
    It looks great. Thank you very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •