Post reply

String manipulation

  • December 15, 2009 at 10:23 am

    #136854

    I have a data field loaded from a flat file. One of the fields looks like below

    jcc-edi-3344-45

    jccc-dfrr-555-3d

    fcdd-fd-4r-3d

    I need to manipulate the 1st 2 parts of the data separated by the -, in other words everything to the left of the 1st -, and the 2nd item following the 1st -. Would an array function work here, or something else considering I only want the 1st two parts of the field

  • December 15, 2009 at 10:49 am

    #1092642

    seems like this function would help you here from the scripts contribution section:

    http://www.sqlservercentral.com/scripts/30497/

    it lets you find the N-th occurrance of the dash for example...

    so if you need everything LEFT of the second dash, it would be

    SELECT SUBSTRING(COLUMNNAME,1,dbo.CHARINDEX2('-', COLUMNNAME, 2) ) FROM YOURTABLE

    or if you want to pull out those first two items as seperate values:

    --results:

    COLUMNNAME COL1 COL2

    ------------------------------ ------------------------------ ------------------------------

    jcc-edi-3344-45 jcc edi

    jccc-dfrr-555-3d jccc dfrr

    fcdd-fd-4r-3d fcdd fd

    CREATE TABLE YOURTABLE(COLUMNNAME varchar(30))

    INSERT INTO YOURTABLE

    SELECT 'jcc-edi-3344-45' UNION ALL

    SELECT 'jccc-dfrr-555-3d' UNION ALL

    SELECT 'fcdd-fd-4r-3d'

    SELECT COLUMNNAME,

    SUBSTRING(COLUMNNAME,1,dbo.CHARINDEX2('-', COLUMNNAME, 1)-1 ) AS COL1,

    SUBSTRING(COLUMNNAME,dbo.CHARINDEX2('-', COLUMNNAME, 1)+1, dbo.CHARINDEX2('-', COLUMNNAME, 2) - dbo.CHARINDEX2('-', COLUMNNAME, 1)-1) AS COL2

    FROM YOURTABLE

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 15, 2009 at 3:07 pm

    #1092743

    What is the dbo.charindex2?

  • December 15, 2009 at 3:09 pm

    #1092748

    Duh sorry forgot to create the function

  • December 16, 2009 at 9:57 am

    #1093162

    Found an interesting issue, if you only have 2 values like

    ased-werd

    it fails with

    Msg 536, Level 16, State 5, Line 1

    Invalid length parameter passed to the SUBSTRING function.

  • December 16, 2009 at 10:02 am

    #1093169

    yeah you'd want to add something like WHERE dbo.CHARINDEX2('-', COLUMNNAME, 2) > 0

    to your query, so you only select things that match the style of two dashes to chop into other columns.

    you need that because obviously not ALL of your data is formatted the way you showed in your example;

    then you'd do a SECOND pass to do the items that have only one dash

    WHERE dbo.CHARINDEX2('-', COLUMNNAME, 1) > 0

    AND dbo.CHARINDEX2('-', COLUMNNAME, 2) < = 0

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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