Not able to separate delimited data in sqlserver

Question

I have column named Description, in that the rows are inserted along with a delimiter '-'.

I used the query to separate it. The query is mentioned below

select Description from Sheet1$ 
cross apply
 SplitString('Description','-')

The columns have following data

Description
00000131-125
0000154-4625-4569-4568-45213

There's no `SplitString` in T-SQL and tables can't be named `Sheet1$`. This looks like a query used to load data from an Excel file. BTW SQL Server 2008 *and* R2 go out of [even extended support](https://blogs.msdn.microsoft.com/sqlreleaseservices/end-of-mainstream-support-for-sql-server-2008-and-sql-server-2008-r2/) in a couple of weeks. Mainstream support ended in 2014. — Panagiotis Kanavos, Jun 14 '19 at 07:39
I am guessing you imported that data from Excel sheet and created SplitString function to split the string? I suggest debugging your function code. and is there any error message when you run the query? — Yeou, Jun 14 '19 at 07:41
@PanagiotisKanavos There is no way to do that having a table name Sheet1$ — Niranjan S, Jun 14 '19 at 07:43
SQL Server 2016 added the [STRING_SPLIT](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017) function to split strings. Before that, various techniques were used to split strings. The fastest ones are using SQLCLR or XML. All of the techniques are described in [Aaron Bertrand's articles](https://sqlperformance.com/2012/07/t-sql-queries/split-strings). `SplitString` is the name in that article for the various techniques, eg `SringSplit_XML` or `SplitStrings_CLR` — Panagiotis Kanavos, Jun 14 '19 at 07:45
@NiranjanS there is no `SplitString` method unless you create one, no matter what the table is called. Where did this query come from? Whoever wrote it probably wrote that `SplitString` method too. I added a link to those methods in the previous comment — Panagiotis Kanavos, Jun 14 '19 at 07:46
@Larnu unfortunately the accepted answer there is the slowest way possible. The *other* answers are far preferable. Including of course, Aaron Bertrand's answer — Panagiotis Kanavos, Jun 14 '19 at 07:52
The accepted answer is, I agree, but Aaron's isn't. Unfortunately there are a lot of high voted bad answers on SO; best thing we can do is down vote them, @PanagiotisKanavos — Thom A, Jun 14 '19 at 07:53

Sreenu131 · Accepted Answer · 2019-06-14T08:07:38.173

0

Try this below

DECLARE @Str AS TABLE ([Description] varchar(max) )
INSERT INTO @Str
SELECT '00000131-125'   UNION ALL
SELECT '0000154-4625-4569-4568-45213'


SELECT Id,
       LTRIM(RTRIM(Split.a.value('.','nvarchar(100)'))) AS [Description]    
FROM
(
    SELECT  
       ROW_NUMBER()OVER(ORDER BY (SELECT Null)) AS Id, 
       CAST('<S>'+(REPLACE([Description],'-','</S><S>')+'</S>') AS XML ) AS [Description]
    FROM @Str
)AS A
CROSS APPLY [Description].nodes('S') AS Split(a)

Adding FOR XML PATH to the end of a query allows you to output the results of the query as XML elements, with the element name contained in the PATH argument.

Result

Id  Description
---------------
1   00000131
1   125
2   0000154
2   4625
2   4569
2   4568
2   45213

edited Jun 14 '19 at 08:07

answered Jun 14 '19 at 07:47

Sreenu131

2,476
1
7
18

You'll have to explain what this is and why it works. Don't just copy code from other SO questions or articles. The *useful* code isn't visible either. This is the XML technique, shown in dozens of SO answers. `ROW_NUMBER()` isn't useful – Panagiotis Kanavos Jun 14 '19 at 07:49
The XML technique can't handle illegal XML characters either, a *very* important problem. It will choke on <,> or & – Panagiotis Kanavos Jun 14 '19 at 07:50

Not able to separate delimited data in sqlserver

1 Answers1