Post reply

Splitting Text Field into individual Columns

  • October 25, 2016 at 3:59 pm

    #400938

    I have a table with a Notes Field Called NOTES with the following content "C1 User Unable to Log in C2 User did not set password Correctly C3 Assisted User with correct password set up"

    I am trying to split the field into 3 columns to represent the contents between C1 and C2 as one field, Contents Between C2 and C3 as a second field, and the contents between C3 and EOL as the third field. My logic works in MS Access 2010 but I am now transferring the programing to MS SQL (2010 I think). I have the following code but it is giving me an "Invalid length parameter passed to the LEFT or SUBSTRING function" error.

    LTRIM(SUBSTRING(DSC.Notes, CHARINDEX('C1', DSC.Notes)+2,(CHARINDEX('C2',REVERSE(DSC.Notes))-1)-(CHARINDEX('C1', DSC.Notes)+2)))

    Any suggestions on how to fix my logic in MS SQL?

    thanks

  • October 25, 2016 at 4:23 pm

    #1908781

    CHARINDEX requires 3 arguments, not 2.

    I would start by doing something simple...

    declare 3 variables of type INT and then get the position of C1, C2, C3. Then get the stuff in between, snipping off stuff at the ends.

    Don't build a function in one go... build a piece (like finding the positions of C1,2,3 Then get the chunks in between).

  • October 25, 2016 at 4:32 pm

    #1908782

    thanks

  • October 25, 2016 at 4:51 pm

    #1908784

    You're welcome. Post back if you need more help. Here's my stub code.

    use tempdb;

    GO

    DECLARE @TestString VARCHAR(250) = 'C1 User Unable to Log in C2 User did not set password Correctly C3 Assisted User with correct password set up';

    SELECT CHARINDEX('C1',@TestString,1)

    , CHARINDEX('C2',@TestString,1)

    , CHARINDEX('C3', @TestString,1);

    returns 1, 26, 65

    So you add 2 or 3 to the position and take everything up to that (26) minus 1 and then TRIM it. etc

  • October 25, 2016 at 7:17 pm

    #1908790

    rlevy 18902 (10/25/2016)

    I have a table with a Notes Field Called NOTES with the following content "C1 User Unable to Log in C2 User did not set password Correctly C3 Assisted User with correct password set up"

    I am trying to split the field into 3 columns to represent the contents between C1 and C2 as one field, Contents Between C2 and C3 as a second field, and the contents between C3 and EOL as the third field. My logic works in MS Access 2010 but I am now transferring the programing to MS SQL (2010 I think). I have the following code but it is giving me an "Invalid length parameter passed to the LEFT or SUBSTRING function" error.

    LTRIM(SUBSTRING(DSC.Notes, CHARINDEX('C1', DSC.Notes)+2,(CHARINDEX('C2',REVERSE(DSC.Notes))-1)-(CHARINDEX('C1', DSC.Notes)+2)))

    Any suggestions on how to fix my logic in MS SQL?

    thanks

    What is the maximum number of "fields" that you expect or would like to code for?

    --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)

  • October 25, 2016 at 8:04 pm

    #1908792

    Jeff,

    My first thought was to be lazy and use DelimitedSplit8K but that would turn each occurrence into a separate record, right?

  • October 25, 2016 at 10:03 pm

    #1908795

    pietlinden (10/25/2016)

    Jeff,

    My first thought was to be lazy and use DelimitedSplit8K but that would turn each occurrence into a separate record, right?

    Yes. You'd have to do a CROSS TAB or PIVOT to "pivot" the resulting column back to a single row of multiple columns. Personally, I'd let it sit as an EAV instead of pivoting back to a row. It's just as easy to select from and you could still pivot it if you needed to but only if you needed to.

    A cCA (Cascading Cross Apply) or cCTE (Cascading CTE) might work well for this, although it would be for a predetermined number of "fields" rather than an unknown number.

    [EDIT] Nah... bad idea. It would be ok if the delimiters were identical.

    --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)

  • October 25, 2016 at 10:41 pm

    #1908796

    Maybe if you're trying to do all this to a table, the cCA method might improve performance a bit because you can then take advantage of the starting position parameter of CHARINDEX and also DRY the code out a bit.

    --DROP TABLE #MyHead

    ;

    SELECT d.RowNum,d.SomeString

    INTO #MyHead

    FROM (

    SELECT 1,'C1 User Unable to Log in C2 User did not set password Correctly C3 Assisted User with correct password set up' UNION ALL

    SELECT 2,'C1 Aye C2 Bee C3 See' UNION ALL

    SELECT 3,'C1 One C2 Two C3 Three'

    )d(RowNum,SomeString)

    ;

    SELECT RowNum

    ,F1 = SUBSTRING(SomeString, ca1.F1Start+3, ca2.F2Start-ca1.F1Start-4)

    ,F2 = SUBSTRING(SomeString, ca2.F2Start+3, ca3.F3Start-ca2.F2Start-4)

    ,F3 = SUBSTRING(SomeString, ca3.F3Start+3, 8000)

    FROM #MyHead t

    CROSS APPLY (SELECT CHARINDEX('C1',SomeString )) ca1 (F1Start)

    CROSS APPLY (SELECT CHARINDEX('C2',SomeString,cA1.F1Start)) ca2 (F2Start)

    CROSS APPLY (SELECT CHARINDEX('C3',SomeString,cA2.F2Start)) ca3 (F3Start)

    ;

    It's not my original idea, though. I got it from Eirikur Erikson. Gotta give the man some credit here.

    --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)

Viewing 8 posts - 1 through 7 (of 7 total)

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