Solved

TSQL Split function

Posted on 2016-08-03
9
45 Views
Last Modified: 2016-10-05
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
0
Comment
Question by:mani_sai
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
A very efficient, probably "best practice", splitter is dbo.DelimitedSplit8K.  You can Google for the source.
0
 
LVL 8

Author Comment

by:mani_sai
Comment Utility
not just splitting, I want the second string (added with first strings first 4 characters as shown in my example...)
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
0
 
LVL 6

Expert Comment

by:Manju
Comment Utility
If the format and length remains same, why can't you use simple left function?

Left(columnname,9)
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 6

Expert Comment

by:Manju
Comment Utility
My bad, ignore my previous comment
0
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 500 total points (awarded by participants)
Comment Utility
Is something like this what you are looking for?  If like you said "( the string format and length remains the same)"
CREATE TABLE test (id INT, val VARCHAR(20));
INSERT INTO dbo.test ( id, val )
VALUES  (1, 'CNCS01306/01307 ' ), (2, 'CNCS01308/01309' );
GO

ALTER FUNCTION dbo.fnSimpleSplit (@input VARCHAR(200))
RETURNS TABLE
AS
RETURN (
SELECT SUBSTRING(@input,1,9) NewVal
UNION
SELECT SUBSTRING(@input,1,4)+SUBSTRING(@input,11,5) 
);
GO

SELECT T.id, NewVal
FROM dbo.test T
CROSS APPLY dbo.fnSimpleSplit(val) x

DROP TABLE dbo.test;

Open in new window

Returns data like this for the 2 records I put in the test table:
Query Results
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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

0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
@Author - Are you satisfied with the above solutions ? or you need some more info.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Closing since the author has not returned.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now