CODEWITHSUNDEEP
sql-server-2008
user2341471
user2341471

Reputation: 49

How to split string in sql server and put it in a table

My input is like 

(abcd@123, xyz@324, def@567)

I want an output 

col1   Col2
abcd   123
xyz    324
def    567

and so on

Column 1 should have abcd and xyz, and column 2 should have 123 and 324 both in different rows and so on. and the String can be of any size.

Thank you

Upvotes: 1

Views: 79

Answers (3)

KnusperPudding
KnusperPudding

Reputation: 412

your actual problem is turning a complex String into a table i gues. That for i found help with a procedure about 1 year ago, that does exactly that:

CREATE FUNCTION [dbo].[fnSplitString] (
@myString varchar(500),
@deliminator varchar(10))
RETURNS 
@ReturnTable TABLE (
[id] [int] IDENTITY(1,1) NOT NULL,
[part] [varchar](50) NULL
)
AS
BEGIN
    Declare @iSpaces int
    Declare @part varchar(50)

    Select @iSpaces = charindex(@deliminator,@myString,0)
    While @iSpaces > 0
    BEGIN
        Select @part = substring(@myString,0,charindex(@deliminator,@myString,0))

        Insert Into @ReturnTable(part)
        Select @part

        Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0))
        Select @iSpaces = charindex(@deliminator,@myString,0)
    END

    If len(@myString) > 0
        Insert Into @ReturnTable
        Select @myString
RETURN
END

Create this procedure and you can do:

DECLARE @TestString varchar(50)
SET @TestString = 'abcd@123,xyz@324,def@567'
Select * from dbo.fnSplitString(@TestString, ',')

Result:

id|     part
1 |     abcd@123
2 |     xyz@324
3 |     def@567

this part you can combine with Leonardos answer:

SELECT 
LEFT(part, CHARINDEX('@', part)-1) As Col1,
RIGHT(part, LEN(part) - CHARINDEX('@', part)) As Col2
from dbo.fnSplitString(@TestString, ',')

to get your problem solved.

(little note: the function has little issues with whitespaces, so please try to avoid them there)

Upvotes: 1

Leonardo Hernández
Leonardo Hernández

Reputation: 191

Try this 

SELECT LEFT('abcd@123', CHARINDEX('@', 'abcd@123')-1),
       RIGHT('abcd@123', CHARINDEX('@', 'abcd@123')-1)

Upvotes: 1

uncoder
uncoder

Reputation: 1886

You will need to use CHARINDEX() and SUBSTRING() functions to split your input values.

Upvotes: 1

Related Questions