How to split this

Question

How to split this

rathnamdamineni

SSC Journeyman

Points: 96
More actions
November 6, 2008 at 10:28 pm

#92816

Hi experts
in my table one column datatype is varchar(200)
in this column user can insert data in these form
('AA1000','2008-20000' , '2009@20000'.....)
i need to split those values as shown below
example:
1. 'AA1000' i need to split this as 'AA' and '1000'
2. '2008-20000' i need to split this as '2008-' and '20000'
3. '2009@20000' i need to split this as '2009@' and '20000'

Viewing 3 posts - 1 through 2 (of 2 total)

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

krayknot SSCertifiable Points: 7998 More actions · Answer 1

rathnamdamineni (11/6/2008)
Hi experts
in my table one column datatype is varchar(200)
in this column user can insert data in these form
('AA1000','2008-20000' , '2009@20000'.....)
i need to split those values as shown below
example:
1. 'AA1000' i need to split this as 'AA' and '1000'
2. '2008-20000' i need to split this as '2008-' and '20000'
3. '2009@20000' i need to split this as '2009@' and '20000'

Use this function

if exists (select * from dbo.sysobjects where id =

object_id(N'[dbo].[UTILfn_Split]') and xtype in (N'FN', N'IF', N'TF'))

drop function [dbo].[UTILfn_Split]

GO

create function dbo.UTILfn_Split(

@String nvarchar (4000),

@Delimiter nvarchar (10)

)

returns @ValueTable table ([Value] nvarchar(4000))

begin

declare @NextString nvarchar(4000)

declare @Pos int

declare @NextPos int

declare @CommaCheck nvarchar(1)

--Initialize

set @NextString = ''

set @CommaCheck = right(@String,1)

--Check for trailing Comma, if not exists, INSERT

--if (@CommaCheck <> @Delimiter )

set @String = @String + @Delimiter

--Get position of first Comma

set @Pos = charindex(@Delimiter,@String)

set @NextPos = 1

--Loop while there is still a comma in the String of levels

while (@pos <> 0)

begin

set @NextString = substring(@String,1,@Pos - 1)

insert into @ValueTable ( [Value]) Values (@NextString)

set @String = substring(@String,@pos +1,len(@String))

set @NextPos = @Pos

set @pos = charindex(@Delimiter,@String)

end

return

end

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

This is even simpler

-- Prepare sample data

DECLARE@Sample TABLE

(

data VARCHAR(20)

)

INSERT@Sample

SELECT'1000' UNION ALL

SELECT'bb' UNION ALL

SELECT'AA1000' UNION ALL

SELECT'2008-20000' UNION ALL

SELECT'2009@20000'

-- Show the expected output

SELECTdata,

LEFT(data, pos) AS part1,

SUBSTRING(data, pos + 1, 20) AS part2

FROM(

SELECTLEN(data) - PATINDEX('%[^0-9]%', REVERSE('#' + data)) + 1 AS pos,

data

FROM@Sample

) AS d

N 56°04'39.16"
E 12°55'05.25"