split character from string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

split character from string

Author

Topic

urzsuresh
Starting Member

30 Posts

Posted - 2010-08-16 : 03:26:52

Hello friends
I have table employee
for eg


1 Ramesh
2 Rahul

I need output like this


1 r
1 a
1 m
1 e
1 s
1 h
2 r
2 a
2 h
2 u
2 l

I need bring this result,without using functions and loop.how can i achieve this. can anyone please suggest me through sample code

Suri

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-16 : 03:42:01

[code]DECLARE @Sample TABLE
(
ID INT,
Name VARCHAR(20)
)

INSERT @Sample
SELECT 1, 'Ramesh' UNION ALL
SELECT 2, 'Rahul'

SELECT s.ID,
LOWER(f.theChar) AS theChar
FROM @Sample AS s
CROSS APPLY (
SELECT SUBSTRING(s.Name, v.Number, 1),
v.Number
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.Number BETWEEN 1 AND DATALENGTH(s.Name)
) AS f(theChar, thePosition)
ORDER BY s.ID,
f.thePosition[/code]

N 56°04'39.26"
E 12°55'05.63"

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-08-16 : 03:44:45

Try this too..

Declare @i int
Declare @name varchar(25)
set @i=1
While (@i<=2)
Begin
Select @name=name from #temp where id=@i
While(len(@name)>0)
Begin
Select @i as id,left(@name,1) as name
set @name=right(@name,len(@name)-1)
End
set @i=@i+1
End

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-16 : 04:36:54

quote:
Originally posted by senthil_nagore

Try this too..

Declare @i int
Declare @name varchar(25)
set @i=1
While (@i<=2)
Begin
Select @name=name from #temp where id=@i
While(len(@name)>0)
Begin
Select @i as id,left(@name,1) as name
set @name=right(@name,len(@name)-1)
End
set @i=@i+1
End

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

how can this be applied on the table data ?

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-16 : 04:49:21

quote:
Originally posted by vaibhavktiwari83

how can this be applied on the table data ?

The code assumes the data is already in the #Temp table.

N 56°04'39.26"
E 12°55'05.63"

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-16 : 05:19:15

quote:
Originally posted by Peso

quote:
Originally posted by vaibhavktiwari83

how can this be applied on the table data ?
The code assumes the data is already in the #Temp table.

N 56°04'39.26"
E 12°55'05.63"

Ohh I misunderstood something but still.
It will give different result set
rather than rows in one result set, won't it ?

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-16 : 05:34:03

Yes, Senthil's suggestion will give you one resultset for every character.
My suggestion will give one resultset for all characters. Quite a difference.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources