Link to home
Start Free TrialLog in
Avatar of Mani Pazhana
Mani PazhanaFlag for United States of America

asked on

TSQL Split function

Hello Experts,
I am using SQL Server 2008:

I want a write a SQL function that will split the following string  by backslash delimiter and return a table..

Input String:   CNCS01306/01307  ( the string format and length remains the same)

Output:
CNCS01306
CNCS01307

Any help?


Thanks
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

A very efficient, probably "best practice", splitter is dbo.DelimitedSplit8K.  You can Google for the source.
Avatar of Mani Pazhana

ASKER

not just splitting, I want the second string (added with first strings first 4 characters as shown in my example...)
Nah, still basically just a split, you just have to get the prefix first, then split the string minus the prefix, and add the prefix back to the front of every item afterward :) :

DECLARE @string varchar(8000)
SET @string = 'CNCS01306/01307/1309/241/99908765'

SELECT ca1.string_prefix + ds.Item
FROM (
    SELECT @string AS string
) AS base_data
CROSS APPLY (
    SELECT LEFT(string, PATINDEX('%[0-9]%', string) - 1) AS string_prefix
) AS ca1
CROSS APPLY dbo.DelimitedSplit8K (SUBSTRING(string, LEN(string_prefix) + 1, 8000), '/') ds
If the format and length remains same, why can't you use simple left function?

Left(columnname,9)
My bad, ignore my previous comment
ASKER CERTIFIED SOLUTION
Avatar of Chris Luttrell
Chris Luttrell
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
check this. If this works for you, you can create a function and with this logic and call it in your code.
declare @str varchar(100) = 'CNCS01306/01307'

select case when n > 1 then left(string,4) else '' end + ltrim(SUBSTRING(string, n, CHARINDEX('/', string + '/',n) - n)) split_string
  from (select @str string) t1
  CROSS JOIN (SELECT number FROM master..spt_values WHERE type = 'P')  AS Numbers(n)
 WHERE SUBSTRING('/' + string, n, 1) = '/'

Open in new window

@Author - Are you satisfied with the above solutions ? or you need some more info.
Closing since the author has not returned.