Split AlphaNumeric

Question

Split AlphaNumeric

PJ_SQL

SSCertifiable

Points: 6703
More actions
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.

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

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

Smendle Hall of Fame Points: 3969 More actions · Answer 1

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

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 2

It is not sometimes it can be

MDL-9054-1

Or It could be like this as well:

TX GMS-7000

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

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

ScottPletcher SSC Guru Points: 101091 More actions · Answer 4

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

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

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 6

How do I split Characters and numbers from sql:

I have this data :DEP-A CALLEJ00025000

Need

cola colb

DEP-A CALLEJ 00025000

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

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

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 8

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

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

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 10

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.

patilar SSC Veteran Points: 218 More actions · Answer 11

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 .