Post reply

Get data from a string

  • January 27, 2011 at 7:16 pm

    #1279478

    PSB (1/27/2011)

    If I use this string below

    DECLARE @TestStr VARCHAR(MAX)

    SET @TestStr=' 5305 32909033 10-06-09 I EDC NCR MCD CASH DISPENSER'

    select

    SUBSTRING(@TestStr, PATINDEX('%[0-9][0-9]-[0-9][0-9]-[0-9][0-9]%|%',@TestStr), 8) as CharDate,

    CAST(SUBSTRING(@TestStr, PATINDEX('%[0-9][0-9]-[0-9][0-9]-[0-9][0-9]%|%',@TestStr), 8) as datetime) as DateVal,

    RIGHT(@TestStr, LEN(@TestStr) - PATINDEX('%|%', @TestStr)) as ItemDescription;

    I get the below result:

    CharDate DateVal ItemDescription

    5305 5305-01-01 00:00:00.000 5305 32909033 10-06-09 I EDC NCR MCD CASH DISPENSER

    Instead of

    CharDate DateVal ItemDescription

    10-07-09 2009-10-07 00:00:00.000 EDC TRITON 9700 CASH DISPENSER

    Your VERTICAL BAR (|) isn't a VERTICAL BAR (|). On my system it is a capital 'I'. If I change it to an actual VERTICAL BAR (|), the code works just fine.

Viewing post 16 (of 15 total)

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