Post reply

spliting the data using replace function

  • December 9, 2009 at 6:40 pm

    #214659

    Hi friends

    I have one coulmn in my table ,it has data look like this..

    Number=122time=1209/n/n/nonsite=1240/n/n

    when i create the report ,the colmn is displaying like this

    Number=122

    time=1209

    nonsite=1240

    what i want here i want to display the above text as 3 columns ,

    any help would be appreiciared...

    tahnks,,,

  • December 9, 2009 at 10:40 pm

    #1090347

    Divide'n'Conquer...

    DECLARE @TestString VARCHAR(100);

    SELECT @TestString = 'Number=122time=1209/n/n/nonsite=1240/n/n';

    WITH

    cteStart AS

    (

    SELECT SUBSTRING(@TestString,CHARINDEX('Number=' ,@TestString)+7,100) AS NumberStart,

    SUBSTRING(@TestString,CHARINDEX('time=' ,@TestString)+5,100) AS TimeStart,

    SUBSTRING(@TestString,CHARINDEX('nonsite=',@TestString)+8,100) AS NonSiteStart

    )

    SELECT SUBSTRING(NumberStart ,1,CHARINDEX('time=',NumberStart )-1) AS Number,

    SUBSTRING(TimeStart ,1,CHARINDEX('/n' ,TimeStart )-1) AS Time,

    SUBSTRING(NonSiteStart,1,CHARINDEX('/n' ,NonSiteStart)-1) AS NotSite

    FROM cteStart;

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 10, 2009 at 12:13 am

    #1090371

    Thanks jeff,i really appreciate your help,,,,,,,,,,,

  • December 10, 2009 at 12:26 am

    #1090376

    You bet, Anitha. Thank you for the feedback.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 10, 2009 at 1:35 am

    #1090392

    Hi jeff

    Sorry for giving trouble to you,,,,,,

    i am in trouble in witing sql for below problem

    My column has data like this

    Number:122

    time:1209

    nonsite:(menas null)

    verbal:out control

    What i want

    Number time nonsite verbal

    122 1209 out conrol

    Can you please help me

  • December 10, 2009 at 1:37 am

    #1090393

    Plaese ask me if you want more information,,,,

    thanks ,,,

  • December 10, 2009 at 7:06 am

    #1090517

    I won't be able to get to this until I get home from work. If you study the code I've offered so far, you'll probably figure it out for yourself in no time. If you can't, could you post the "flat line" that appears like you did in your original post? Thanks.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 10, 2009 at 5:04 pm

    #1090927

    Hi Jeff

    thanks for time.

    First i used char(13)+Char(10) function to find out enter symbols(Carraige returns)

    then my clomn is displaying like this...

    test='GUARD NUMBER:NC122 TauZZDISPATCH TIME:1907ZZTIME ONSITE:1930ZZTIME OFFSITE:ZZOUTCOME:Verbal WarningZZ'

    what i want here

    guard number=nC122 Tau

    Dispatch time=1970

    time onsite=1930

    time offsite=

    outcome =verbal warning,

    note :zz means i am displaying zz value as default when carriage return found in text.

  • December 10, 2009 at 5:05 pm

    #1090928

    thanks once again...

  • December 10, 2009 at 5:58 pm

    #1090931

    Ok... here you go. You need to study this, Anitha, because every string like this follows a similar pattern of FieldName:fieldvalue fieldterminator. You should be able to do this on your own whenever you run into something like this. Read the comments in the code below for what each piece of code means. You'll be an expert on this in no time. 😉 And, yes, with just a minor tweek here and there, you can do a whole table at once without using a UDF, cursor, while loop, recursion, or other form of RBAR. 😀

    DECLARE @TestString VARCHAR(256),

    @MaxLength INT;

    SELECT @MaxLength = 256; --Should be same as the VARCHAR() definition of @TestString above or column in a table

    SELECT --This just builds the test string and isn't a part of the actual solution.

    @TestString = 'GUARD NUMBER:NC122 TauZZDISPATCH TIME:1907ZZTIME ONSITE:1930ZZTIME OFFSITE:ZZOUTCOME:Verbal WarningZZ',

    @TestString = REPLACE(@TestString,'ZZ',CHAR(13)+CHAR(10)); --Replaces "ZZ" with a CrLf to simulate the real text.

    WITH

    cteStart AS

    (

    --Each field gets it's own SUBSTRING here. Finds the "start" of each field and remembers from there to the end.

    --Put bogus field names over here >>>---------------------------------------------------------|

    --Put length of field header found here >>>---| |

    -- | ... over here >>>---------| |

    -- (these are from the string) |-----------| | |

    -- V V V V

    SELECT SUBSTRING(@TestString, CHARINDEX('GUARD NUMBER:' ,@TestString) + 13, @MaxLength) AS F1Start,

    SUBSTRING(@TestString, CHARINDEX('DISPATCH TIME:' ,@TestString) + 14, @MaxLength) AS F2Start,

    SUBSTRING(@TestString, CHARINDEX('TIME ONSITE:' ,@TestString) + 12, @MaxLength) AS F3Start,

    SUBSTRING(@TestString, CHARINDEX('TIME OFFSITE:' ,@TestString) + 13, @MaxLength) AS F4Start,

    SUBSTRING(@TestString, CHARINDEX('OUTCOME:' ,@TestString) + 8, @MaxLength) AS F5Start

    )

    --Each field gets it's own SUBSTRING here, too. Finds the "end" of each field and returns substring for field.

    --Put desired column name for each field here >>>---------------------|

    --Will always be -1 here >>>---------------------------------| |

    --Bogus field name from the CTE above >>>------------| | |

    --Whatever the field ends with >>>---------| | | |

    --Always "1" >>>-----------| | | | |

    --Bogus field name --| | | | | |

    -- V V V V V V

    SELECT SUBSTRING(F1Start ,1, CHARINDEX(CHAR(13), F1Start ) -1) AS Guard_Number,

    SUBSTRING(F2Start ,1, CHARINDEX(CHAR(13), F2Start ) -1) AS Dispatch_Time,

    SUBSTRING(F3Start ,1, CHARINDEX(CHAR(13), F3Start ) -1) AS Time_Onsite,

    SUBSTRING(F4Start ,1, CHARINDEX(CHAR(13), F4Start ) -1) AS Time_OffSite,

    SUBSTRING(F5Start ,1, CHARINDEX(CHAR(13), F5Start ) -1) AS Outcome

    FROM cteStart;

    We could write some code to automatically discover the field names and have the program actually write the code above, but you have to learn how to do it manually first so you can troubleshoot automatic code.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 10, 2009 at 6:26 pm

    #1090938

    I really thankfull to you,you are so wondefull .........

  • December 10, 2009 at 6:34 pm

    #1090941

    Thanks, Anitha :blush:

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 10, 2009 at 6:42 pm

    #1090943

    thanks jeff

    you are wonderfull ,

  • December 10, 2009 at 8:35 pm

    #1090968

    Hi jeff

    i am very to sorry to disturb you,

    your query is awesome,

    but i have to include your bit in my select statement.

    here is the code..

    SELECT

    document.RegdAt AS [Date Logged],

    document.DocNo AS [Request No],

    Action.NoteText AS NoteTEXT---------------here i have to use your bit to display different columns

    FROM dbo.Document

    INNER JOIN Action on Action.DocNo = document.DocNo

    AND (document.RegdAt BETWEEN @DTStart AND @DTEnd)

    ORDER BY document.DocNo

    please let me know if you want any information

  • December 10, 2009 at 8:55 pm

    #1090971

    Save the output of your query into a table with the extra columns you need. Then, use my query to UPDATE the table.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 15 posts - 1 through 15 (of 26 total)

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