How to split character from string

Question

Post reply

How to split character from string

KMPSSS

Ten Centuries

Points: 1052
More actions
August 16, 2010 at 12:37 am

#227942

Hello Friends,
I have table employee
Datas are like this 1 Ramesh
2 Rahul
Need output
1 r
1 a
1 m
1 e
1 s
1 h
2 r
2 a
2 h
2 u
2 l
I need to bring this result without using function and while loops.how can i achieve this. can any one plz explain through sample code

Viewing 8 posts - 1 through 7 (of 7 total)

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

mayank_tyagi Old Hand Points: 325 More actions · Answer 1

Dear KMPASS ,

It can be solved by very good article for all string related query

with the help of Tally table:

Wriiten by Jeff Moden

http://www.sqlservercentral.com/articles/T-SQL/62867/

features are:

Stepping Through Characters,

do the "Split".

One Final "Split" Trick with the Tally Table

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 2

Taking as an example a 20 character EmployeeName column, we can resolve the 20 individual components using the SUBSTRING function and then use UNPIVOT to flip the data vertically. Using the WHERE clause, we include only the components which have a non-zero datalength.

IF NOT OBJECT_ID('tempdb.dbo.#temp', 'U') IS NULL DROP TABLE #temp

CREATE TABLE #temp (EmployeeID int, EmployeeName varchar(20))

INSERT #temp

SELECT 1, 'Ramesh' UNION ALL

SELECT 2, 'Rahul'

;WITH cte AS

(

SELECT EmployeeID,

SUBSTRING(EmployeeName, 1, 1) AS v1,

SUBSTRING(EmployeeName, 2, 1) AS v2,

SUBSTRING(EmployeeName, 3, 1) AS v3,

SUBSTRING(EmployeeName, 4, 1) AS v4,

SUBSTRING(EmployeeName, 5, 1) AS v5,

SUBSTRING(EmployeeName, 6, 1) AS v6,

SUBSTRING(EmployeeName, 7, 1) AS v7,

SUBSTRING(EmployeeName, 8, 1) AS v8,

SUBSTRING(EmployeeName, 9, 1) AS v9,

SUBSTRING(EmployeeName, 10, 1) AS v10,

SUBSTRING(EmployeeName, 11, 1) AS v11,

SUBSTRING(EmployeeName, 12, 1) AS v12,

SUBSTRING(EmployeeName, 13, 1) AS v13,

SUBSTRING(EmployeeName, 14, 1) AS v14,

SUBSTRING(EmployeeName, 15, 1) AS v15,

SUBSTRING(EmployeeName, 16, 1) AS v16,

SUBSTRING(EmployeeName, 17, 1) AS v17,

SUBSTRING(EmployeeName, 18, 1) AS v18,

SUBSTRING(EmployeeName, 19, 1) AS v19,

SUBSTRING(EmployeeName, 20, 1) AS v20

FROM #temp

)

SELECT EmployeeID, SplitChar

FROM cte

UNPIVOT (SplitChar FOR Comp IN (v1,v2,v3,v4,v5,v6,v7,v8,v9,v10,v11,v12,v13,v14,v15,v16,v17,v18,v19,v20)) AS Z

WHERE NOT DATALENGTH(SplitChar) = 0

Jeff Moden SSC Guru Points: 1004190 More actions · Answer 3

mayank_tyagi (8/16/2010)
Dear KMPASS ,
It can be solved by very good article for all string related query
with the help of Tally table:
Wriiten by Jeff Moden
http://www.sqlservercentral.com/articles/T-SQL/62867/
features are:
Stepping Through Characters,
do the "Split".
One Final "Split" Trick with the Tally Table

Thank you for the honorable mention. 🙂

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

KMPSSS Ten Centuries Points: 1052 More actions · Answer 4

Thank you very much for all your precious reply, i have got some idea, regarding this. I have one doubt, if name lenght exceeds above 20 character.what can i do. Kindly suggest me.

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 5

Is 8000 characters wide enough? 🙂 You should read Jeff Moden's article referenced above which explains the concept of the tally table very well. This is one way to accomplish the split using an in-line tally table.

IF NOT OBJECT_ID('tempdb.dbo.#Employee', 'U') IS NULL DROP TABLE #Employee

CREATE TABLE #Employee (EmployeeID int, EmployeeName varchar(8000))

INSERT #Employee

SELECT 1, 'Ramesh' UNION ALL

SELECT 2, 'Rahul' UNION ALL

SELECT 3, REPLICATE('AnExtremelyBroadName', 400)

;WITH cteTally (N) AS

(

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM master..syscolumns

)

SELECT EmployeeID, SplitChar FROM #Employee

CROSS APPLY

(

SELECT SUBSTRING(EmployeeName, N, 1), N

FROM cteTally

WHERE N < LEN(EmployeeName) + 1

) AS Z (SplitChar, N)

ORDER BY EmployeeID, N

LutzM SSC Guru Points: 107049 More actions · Answer 6

to "quote" Bob Dylan:

"How many chars can I split with this code

before it will starting to fail?

The answer, my friend, is written in the link[/url].

The answer is written in the link[/url]."

😀 (sorry, couldn't resist...)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004190 More actions · Answer 7

Simplify with a real Tally table...

SELECT e.EmployeeID,LOWER(SUBSTRING(e.EmployeeName,t.N,1)) AS SplitChar

FROM #Employee e

CROSS JOIN dbo.Tally t

WHERE t.N <= LEN(e.EmployeeName)

ORDER BY e.EmployeeID

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