Splitting One Row Data into Two Rows

Question

Post reply

Splitting One Row Data into Two Rows

Arjun SreeVastsva

SSCertifiable

Points: 7141
More actions
June 4, 2010 at 6:08 am

#221857

how to split the Splitting One Row Data into Two Rows in a Table

Viewing 15 posts - 1 through 15 (of 19 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Look up UNION or UNPIVOT in BOL.

-- Gianluca Sartori

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 2

Could you be more specific? Maybe give some examples of what you want to do?

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Arjun SreeVastsva SSCertifiable Points: 7141 More actions · Answer 3

sachin123 it can be splitted into sachin one row 123 in other row..

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Raju The Leader (6/4/2010)
sachin123 it can be splitted into sachin one row 123 in other row..

So, you mean splitting a SINGLE char field upon a delimiter?

There's a good split function here[/url].

-- Gianluca Sartori

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 5

And that's one column, yes?

Is it always a split when you start to see numbers or is it after a certain number of characters?

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 6

Check this sample, modify according to your requirement

SET ANSI_NULLS ON

SET ANSI_WARNINGS ON

SET ANSI_PADDING ON

GO

DECLARE@Sample TABLE

(

Code VARCHAR(20) NOT NULL

)

INSERT INTO @Sample

SELECT 'Sachin,123'

DECLARE@xml XML

SELECT@XML = CAST('<Gopi>' + REPLACE(Code, ',', '</Gopi><Gopi>') + '</Gopi>' AS XML) FROM @Sample

SELECTw.value('.', 'VARCHAR(20)')

FROM@XML.nodes('Gopi') AS n(w)

Arjun SreeVastsva SSCertifiable Points: 7141 More actions · Answer 7

Arjun SreeVastsva

SSCertifiable

Points: 7141

June 4, 2010 at 6:42 am

#1176492

we need to spilt the Varchar and Numeric data

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 8

@gopi Muluka

Your example assumes there's a character indicator specifying where the split occurs. His example doesn't show one. If it is a split once a certain position in the field has been reached or once the alpha becomes numeric, I have to go with Gianluca Sartori in suggesting the method in the article he linked to.[/url] While complex it is powerful and fast.

@raju The Leader

If you have trouble with that article, let us know and maybe we can give you some simpler, but less robust methods.

Also, is there a comma or period separating the information you want to split or is it a certain number of characters into the field or is it when it changes from alpha to numeric?

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 9

Raju The Leader (6/4/2010)
we need to spilt the Varchar and Numeric data

Is it always alpha then numeric? In the example you sent me, it was the other way around.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 10

Another assumption .. 🙂

I think he need this

SET ANSI_NULLS ON

SET ANSI_WARNINGS ON

SET ANSI_PADDING ON

GO

DECLARE@Sample TABLE

(

Code VARCHAR(20) NOT NULL

)

INSERT INTO @Sample

SELECT '22G' UNION

SELECT '10A' UNION

SELECT '45B'

SELECT LEFT(Code,PATINDEX('%[a-z]%',Code)-1) AS NUM1,SUBSTRING(Code,PATINDEX('%[a-z]%',Code),LEN(Code)) AS CHAR1 FROM @Sample

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 11

Gopi Muluka (6/4/2010)
Another assumption .. 🙂
I think he need this

Yeah, that could work if he doesn't want to use the TallyTable for some reason. Though from his examples, I think he'd be looking for the first instance of an integer instead of an A-Z char, which in some ways is easier as he won't have to consider case.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

spaghettidba SSC Guru Points: 105732 More actions · Answer 12

Another assumption: 🙂

CREATE FUNCTION fSplitNumeric(@parameter nvarchar(4000))

RETURNS TABLE

AS

RETURN (

WITH Data AS (

SELECT N,

S = SUBSTRING(@Parameter,N,1),

G = CASE WHEN SUBSTRING(@Parameter,N,1) LIKE '[0-9]' THEN 1 ELSE 0 END

FROM Tally

WHERE N <= LEN(@Parameter)

),

RankedData AS (

SELECT *, R = DENSE_RANK() OVER(ORDER BY G, N)

FROM Data

),

Tokens AS (

SELECT *

FROM RankedData AS A

CROSS APPLY (

SELECT Token = (

SELECT S AS [text()]

FROM RankedData

WHERE N - R = A.N - A.R

ORDER BY N

FOR XML PATH('')

)

) AS TK

)

SELECT TokenID = ROW_NUMBER() OVER (ORDER BY MIN(N)), Token

FROM Tokens

GROUP BY Token

)

GO

DECLARE @Strings TABLE (

Id int,

String varchar(50)

)

INSERT INTO @Strings

SELECT 1, 'abcde123fghilm12321abc' UNION ALL

SELECT 2, '789nrtglm777trep' UNION ALL

SELECT 3, '987gf654sfd987as7'

SELECT *

FROM @Strings

CROSS APPLY dbo.fSplitNumeric(String)

ORDER BY Id, TokenId

This code assumes you have a numbers table named "Tally" in your database. If you don't have one, read this article[/url]and find out why you need it.

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 13

Dare to Fight (6/8/2010)
Hi
Thanks for help but it was giving error.
Msg 156, Level 15, State 1, Procedure fSplitNumeric, Line 36
Incorrect syntax near the keyword 'DECLARE'.
please help me

OK, let's try to work it out. Please, don't PM me: post here instead, so that everyone can benefit from these forums in case they fall into the same problem.

I edited the code in my previous post and added a 'GO' between the function and the sample code to call the function. Maybe this is what you're looking for, maybe not: take a look at the article linked in my signature line and you'll find out how to post effectively.

Basically, we need:

1) a table script

2) some sample data

3) the desired output

4) what you have tried so far

This makes our replies smarter and faster. Otherwise you'll probably get generic answers that don't solve your issue.

Hope this helps a bit.

-- Gianluca Sartori

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

Gopi Muluka (6/4/2010)
Another assumption .. 🙂
I think he need this
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET ANSI_PADDING ON
GO
DECLARE@Sample TABLE
(
Code VARCHAR(20) NOT NULL
)
INSERT INTO @Sample
SELECT '22G' UNION
SELECT '10A' UNION
SELECT '45B'
SELECT LEFT(Code,PATINDEX('%[a-z]%',Code)-1) AS NUM1,SUBSTRING(Code,PATINDEX('%[a-z]%',Code),LEN(Code)) AS CHAR1 FROM @Sample

If you reverse the postion of the letters and numbers (make the data letters first like in the original post), I believe that's the answer I'd use. As much as I like the Tally Table, I don't believe that it's actually need for this problem.

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