Click here to Skip to main content
14,265,512 members
Rate this:
Please Sign up or sign in to vote.
See more:
how to split a word into sql server and store it into string array or else so that we can retreive its value one by one in sql 2008
Posted
Rate this:
Please Sign up or sign in to vote.

Solution 2

DECLARE @FullName        VARCHAR(100)
SET @FullName = 'John Doe'

SELECT SUBSTRING(@FullName, 1, CHARINDEX(' ', @FullName) - 1) AS [FirstName],
       SUBSTRING(@FullName, CHARINDEX(' ', @FullName) + 1, LEN(@FullName)) AS [LastName]

Ref: Split Name[^]

This SQL
Split Function 
is use to SPLIT a sentences based on the Delimeter.
Delimeter is a string character used to identify substring limits.
Refer: SQL Split Function[^]
   
Comments
ParkashKaur 8-Jun-12 3:35am
   
I am talking about inbuilt function........
Rate this:
Please Sign up or sign in to vote.

Solution 1

See this link

how to split string in sqlserver[^]

Thanks
--RA
   
Rate this:
Please Sign up or sign in to vote.

Solution 3

Hi Prakash,

try this code .

SELECT T1.PRIMARY_KEY,n.r.value('.','varchar(50)') as TypeId FROM TABLE_1 AS T1
  CROSS APPLY (SELECT cast('<r>'+replace(T1.[Names], ',', '</r><r>')+'</r>' as xml)) as S(XMLCol)
  cross apply S.XMLCol.nodes('r') as n(r)
   
Rate this:
Please Sign up or sign in to vote.

Solution 4

--Here it goes:

----------------
-- FieldCount --
----------------
CREATE FUNCTION [dbo].[FieldCount](@S VARCHAR(8000), @Separator VARCHAR(10))
RETURNS INT
AS

BEGIN

/*
@Author: Leonardo Augusto Rezende Santos
@Contact: http://www.linkedin.com/pub/leonardo-santos/0/2b1/890
*/

DECLARE @Ptr INT, @p INT, @LenS INT, @LenSep INT, @Result INT

IF @Separator = ' '
BEGIN
SET @S = REPLACE(@S, ' ', '|-|')
SET @Separator = '|-|'
END

WHILE CHARINDEX(@Separator + @Separator, @S) > 0
SET @S = Replace(@S, @Separator + @Separator, @Separator + '_-_' + @Separator)
IF @S <> ''
SET @Result = 1
ELSE
BEGIN
SET @Result = 0
RETURN(@Result)
END
SET @Ptr = 0
SET @LenS = LEN(@S)
SET @LenSep = LEN(@Separator)
SET @p = CHARINDEX(@Separator, @S)
WHILE @p > 0
BEGIN
SET @Result = @Result + 1
SET @Ptr = @Ptr + @p + @LenSep
SET @p = CHARINDEX(@Separator, SUBSTRING(@S, @Ptr, @LenS - @Ptr + 1))
END

RETURN(@Result)

END



--------------
-- GetField --
--------------
CREATE FUNCTION [dbo].[GetField](@S VARCHAR(8000), @Separator VARCHAR(10), @Field INT)
RETURNS VARCHAR(8000)
AS

BEGIN

/*
@Author: Leonardo Augusto Rezende Santos
@Contact: http://www.linkedin.com/pub/leonardo-santos/0/2b1/890
*/

DECLARE @Ptr INT, @p INT, @LenS INT, @LenSep INT, @Fld INT, @Result VARCHAR(8000)

IF @Separator = ' '
BEGIN
SET @S = REPLACE(@S, ' ', '|-|')
SET @Separator = '|-|'
END

IF @Field > dbo.FieldCount(@S, @Separator)
BEGIN
SET @Result = ''
RETURN(@Result)
END
SET @Fld = 1
SET @Ptr = 1
SET @LenS = LEN(@S)
SET @LenSep = LEN(@Separator)
SET @p = CHARINDEX(@Separator, @S)
WHILE (@p > 0) and (@Fld < @Field)
BEGIN
SET @Fld = @Fld + 1
SET @Ptr = @Ptr + @p + @LenSep - 1
SET @p = CHARINDEX(@Separator, SUBSTRING(@S, @Ptr, @LenS - @Ptr + 1))
END
IF (@p = 0) and (@Fld = @Field)
SET @p = @LenS - @Ptr + 2
SET @Result = SUBSTRING(@S, @Ptr, @p - 1)

RETURN(@Result)

END

/* USAGE*/

select dbo.FieldCount('A1 A2 A3 A4 A5', ' ')

--It will return 5

select dbo.GetField('A1 A2 A3 A4 A5', ' ', 3)

--It will return 'A3'

select dbo.GetField('A1/A2/A3/A4/A5', '/', 3)

--It will return 'A3'

--Hope it works for you.

--Leonardo Augusto
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100