Splitting One Row Data into Two Rows

Question

Splitting One Row Data into Two Rows

Viewing 5 posts - 16 through 19 (of 19 total)

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

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 1

Yes, I too don't think a tally table is needed here.

But I think the code should output two rows -- to match the original request 🙂 -- and be able to handle number then alpha and alpha then number in the same data.

Oh, I also allowed for any other non-numeric charater(s): ),: ...

that might appear in data.

For example:

DECLARE @sample TABLE ( code VARCHAR(20) NOT NULL )

INSERT INTO @sample

SELECT '22G' UNION ALL

SELECT '10A' UNION ALL

SELECT '45B' UNION ALL

SELECT 'abc123' UNION ALL

SELECT 'xyz987'

SELECT CASE

WHEN row# = 1 THEN

LEFT(code, CASE WHEN PATINDEX('%[0123456789]%', code) = 1 THEN PATINDEX('%[^0123456789]%', code) ELSE PATINDEX('%[0123456789]%', code) END - 1)

ELSE

SUBSTRING(code, CASE WHEN PATINDEX('%[0123456789]%', code) = 1 THEN PATINDEX('%[^0123456789]%', code) ELSE PATINDEX('%[0123456789]%', code) END, 2000)

END

FROM @sample

CROSS JOIN (

SELECT 1 AS row# UNION ALL SELECT 2

) AS row#s

Scott Pletcher, SQL Server MVP 2008-2010

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

Understood. You might want to look into the use of [0-9] and [^0-9] just to simplify the code a bit.

Nicely done on the two row "embedded Tally Table" for the row separation.

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

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 3

You might want to look into the use of [0-9] and [^0-9] just to simplify the code a bit.

Except I've had some odd results sometimes from that :unsure:. So, since it's not that much longer, I just code out each number individually :-).

Scott Pletcher, SQL Server MVP 2008-2010

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

scott.pletcher (6/10/2010)
You might want to look into the use of [0-9] and [^0-9] just to simplify the code a bit.
Except I've had some odd results sometimes from that :unsure:. So, since it's not that much longer, I just code out each number individually :-).

I've never had a problem with range notations and LIKE... what kind of odd results have you gotten?

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

Jeff Moden (6/10/2010)
scott.pletcher (6/10/2010)
You might want to look into the use of [0-9] and [^0-9] just to simplify the code a bit.
Except I've had some odd results sometimes from that :unsure:. So, since it's not that much longer, I just code out each number individually :-).
I've never had a problem with range notations and LIKE... what kind of odd results have you gotten?

Scott... I'm still really interested in an example of a digit range going haywire if you have one. Thanks.

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