Post reply

Split AlphaNumeric

  • July 5, 2016 at 1:48 pm

    #308843

    I have column with data such as

    EQ181

    TX 116-3860

    How do I split the above in two different columns?

    ColA ColB

    EQ 181

    TX 116-3860

    Thanks.

  • July 5, 2016 at 1:50 pm

    #1888556

    PJ_SQL (7/5/2016)

    I have column with data such as

    EQ181

    TX 116-3860

    How do I split the above in two different columns?

    ColA ColB

    EQ 181

    TX 116-3860

    Thanks.

    It depends is cola always 2 characters in length?

    Or is there some other rule we can go by like ColA is Alphabetic and ColB is numeric

  • July 5, 2016 at 1:54 pm

    #1888559

    It is not sometimes it can be

    MDL-9054-1

    Or It could be like this as well:

    TX GMS-7000

  • July 5, 2016 at 2:05 pm

    #1888564

    PJ_SQL (7/5/2016)

    It is not sometimes it can be

    MDL-9054-1

    Or It could be like this as well:

    TX GMS-7000

    Dear PJ

    please read the following

    https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

    and then post all possible inputs and outputs required......thanks....I have a feeling that you maybe over simplyfing your requirememts possibly?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • July 5, 2016 at 2:06 pm

    #1888565

    Assuming you want to split first by a space, but if there's no space then by dash:

    SELECT value, LEFT(value, ISNULL(NULLIF(CHARINDEX(' ', value), 0), CHARINDEX('-', value) - 1)) AS split_value

    FROM (

    VALUES('MDL-9054-1'),('TX GMS-7000')

    ) AS test_data(value)

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • July 5, 2016 at 3:26 pm

    #1888578

    Here's a couple solutions based on what your output should look like for the values "MDL-9054-1" and "TX GMS-7000"

    DECLARE @strings TABLE(string varchar(100));

    INSERT @strings VALUES('MDL-9054-1'),('TX GMS-7000');

    SELECT

    ColA = SUBSTRING(string,1,CHARINDEX('-',string)-1),

    ColB = SUBSTRING(string,CHARINDEX('-',string)+1,8000)

    FROM @strings;

    SELECT

    ColA = SUBSTRING(string,1,PATINDEX('%[- ]%',string)-1),

    ColB = SUBSTRING(string,PATINDEX('%[- ]%',string)+1,8000)

    FROM @strings;

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • July 6, 2016 at 12:22 pm

    #1888764

    How do I split Characters and numbers from sql:

    I have this data :DEP-A CALLEJ00025000

    Need

    cola colb

    DEP-A CALLEJ 00025000

  • July 6, 2016 at 12:40 pm

    #1888768

    PJ_SQL (7/6/2016)

    How do I split Characters and numbers from sql:

    I have this data :DEP-A CALLEJ00025000

    Need

    cola colb

    DEP-A CALLEJ 00025000

    Have you tried changing the pattern provided by Alan?

    Can you post sample data in a consumable format, as well as expected results?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 6, 2016 at 12:50 pm

    #1888772

    Sorry to keep changing requirement:

    ColA:

    AAAA-AOP/W 00160000

    ASSSS-DOU/W00160000

    O/OF EEEE 00000000

    So, I need to split the column on the criteria that if it starts with 0 then split it to different column

  • July 6, 2016 at 1:03 pm

    #1888775

    PJ_SQL (7/6/2016)

    Sorry to keep changing requirement:

    ColA:

    AAAA-AOP/W 00160000

    ASSSS-DOU/W00160000

    O/OF EEEE 00000000

    So, I need to split the column on the criteria that if it starts with 0 then split it to different column

    Again:

    Luis Cazares (7/6/2016)

    Have you tried changing the pattern provided by Alan?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 6, 2016 at 1:10 pm

    #1888780

    It didn't work for me.

    Also, if the numeric value starting after 0 is of length 8 only I need to split to other column.

  • July 7, 2016 at 6:24 am

    #1888890

    create table table_1

    (

    col1 varchar(100)

    )

    insert into table_1

    values

    ('EQ181'),

    ('TX 116-3860')

    Select

    col1

    ,SUBSTRING(col1,1,Minimun-1) as col1

    ,SUBSTRING(col1,Minimun,data-Minimun+1)

    from

    (

    select col1,min(N) as Minimun,DATALENGTH(col1) as data from table_1 cross apply Tally

    where SUBSTRING(col1,n,1) like '%[0-9]%'

    group by col1

    ) as a

    You may have to create the tally table to make this code work.

    This splits the data as it finds the 1st number .

Viewing 12 posts - 1 through 11 (of 11 total)

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