String Segregation

Question

String Segregation

vs.satheesh

SSCrazy

Points: 2392
More actions
July 18, 2018 at 7:15 am

#366306

Dear all
In my table one column i have records in following formets
mat201
mat202
sci12
science203
In this above value i want to segregate two different column like a
MAT 201
MAT 202
SCI 12
SCIENCE 201
Please help me above result

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

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

vs.satheesh - Wednesday, July 18, 2018 7:15 AM
Dear all
In my table one column i have records in following formets
mat201
mat202
sci12
science203
In this above value i want to segregate two different column like a
MAT 201
MAT 202
SCI 12
SCIENCE 201
Please help me above result

You could try to do it with substring and patindex. It's not that hard to do.

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

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 2

select upper (substring(col,0,patindex('%[0-9]%',col))+' '+substring(col,patindex('%[0-9]%',@col,99))
from your table

***The first step is always the hardest *******

sgmunson SSC Guru Points: 110639 More actions · Answer 3

SGT_squeequal - Wednesday, July 18, 2018 7:33 AM
select upper (substring(col,0,patindex('%[0-9]%',col))+' '+substring(col,patindex('%[0-9]%',@col,99))
from your table

Almost, but not quite. OP is looking for separate columns, whereas your solution just adds a space in the middle.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Does this help?
DECLARE @TestTable TABLE ( DataCol VARCHAR(32) );

INSERT INTO @TestTable
VALUES
('mat201')
,('mat202')
,('sci12')
,('science203');

SELECT
[tt].[DataCol]
, PATINDEX('%[0-9]%',[tt].[DataCol])
, LEFT([tt].[DataCol],PATINDEX('%[0-9]%',[tt].[DataCol]) - 1)
, SUBSTRING([tt].[DataCol], PATINDEX('%[0-9]%',[tt].[DataCol]), DATALENGTH([tt].[DataCol]))
FROM @TestTable AS [tt];

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 5

sgmunson - Thursday, July 19, 2018 2:15 PM
SGT_squeequal - Wednesday, July 18, 2018 7:33 AM
select upper (substring(col,0,patindex('%[0-9]%',col))+' '+substring(col,patindex('%[0-9]%',@col,99))
from your table
Almost, but not quite. OP is looking for separate columns, whereas your solution just adds a space in the middle.

Arr yes, i was looking at his results and returning the data to match instead of splitting the string

***The first step is always the hardest *******