Mani Pazhana
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
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
A very efficient, probably "best practice", splitter is dbo.DelimitedSplit8K. You can Google for the source.
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
DECLARE @string varchar(8000)
SET @string = 'CNCS01306/01307/1309/241/
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)
Left(columnname,9)
My bad, ignore my previous comment
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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) = '/'
@Author - Are you satisfied with the above solutions ? or you need some more info.
Closing since the author has not returned.