Split string to array

Question

Post reply

Split string to array

sqlbaby2

Ten Centuries

Points: 1351
More actions
November 2, 2010 at 12:47 am

#240540

Hi, can anyone give me a hand ? I have tried lot of methods but all failed :crying:
below is the data and the expected output.. what i need is to split string to array
ID Text
1 Adam Rim Jessica Joel
2 Joe Ken Thomson Olivia_Wong
3 John Mary Jessica_Alba Mary_Joe
4 Eric_Wong Simpson Bob
Excepted output
ID Text
2 Olivia_Wong
3 Jessica_Alba
3 Mary_Joe
4 Eric_Wong

Viewing 5 posts - 1 through 4 (of 4 total)

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

Andreas Goldman Old Hand Points: 313 More actions · Answer 1

Hi there,

Create the function below, and then call it like SELECT * FROM F_ConvertStringToTable('1 Adam Rim Jessica Joel)', ' '):

CREATE FUNCTION F_ConvertStringToTable

(

@List VARCHAR(MAX),-- Separerad lista av värden

@Delimiter CHAR(1)-- Avgränsare/separator

)

RETURNS @T TABLE (Col VARCHAR(MAX) NOT NULL) AS

BEGIN

WITH SEPARATEDTABLE (STARTVAL, STOPVAL)

AS

(

SELECT

STARTVAL = CAST(1 AS BIGINT),

STOPVAL = CHARINDEX(@Delimiter, @List + @Delimiter)

UNION ALL

SELECT

STARTVAL = STOPVAL + 1,

STOPVAL = charindex(@Delimiter, @List + @Delimiter, STOPVAL + 1)

FROM SEPARATEDTABLE

WHERE STOPVAL > 0

)

INSERT @t(Col)

SELECT LTRIM(RTRIM(SUBSTRING(@List, STARTVAL, CASE WHEN STOPVAL > 0 THEN STOPVAL - STARTVAL ELSE 0 END)))

FROM SEPARATEDTABLE

WHERE STOPVAL > 0

OPTION (MAXRECURSION 0)

RETURN

END

Andreas Goldman

sqlbaby2 Ten Centuries Points: 1351 More actions · Answer 2

Hi Goldman,

The UDF was used to broken the list into a result set, with each individual letter as its own row. But it still didn't solve the problem

WayneS SSC Guru Points: 95405 More actions · Answer 3

You didn't specify a few things that I'm assuming.

First, you want to split the string apart at the spaces.

Second, you only want items from the split that contain an underscore.

This solution utilizes the DelimitedSplit8K function. Here is the latest version of the Delimited Split Function.

-- See how you start off by actually creating a table and then

-- inserting the data into it? Your doing this makes it a lot easier

-- for all of us volunteers to help you. So, help us help you.

-- See http://qa.sqlservercentral.com/articles/Best+Practices/61537/

-- for more details on how to do all of this.

DECLARE @test-2 TABLE (ID INT, [Text] varchar(max) );

INSERT INTO @test-2

SELECT 1, 'Adam Rim Jessica Joel' UNION ALL

SELECT 2, 'Joe Ken Thomson Olivia_Wong' UNION ALL

SELECT 3, 'John Mary Jessica_Alba Mary_Joe' UNION ALL

SELECT 4, 'Eric_Wong Simpson Bob';

SELECT t.ID, ds.Item

FROM @test-2 t

CROSS APPLY dbo.DelimitedSplit8K(t.[Text], ' ')ds

WHERE CharIndex('_', ds.Item) > 0

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

sqlbaby2 Ten Centuries Points: 1351 More actions · Answer 4

sqlbaby2

Ten Centuries

Points: 1351

November 3, 2010 at 1:03 am

#1245855

Thanks a lot !!!