Split string to substrings on a character and turn into multiple lines SQL

Question

I have the following table:

+--------+-----------------+
|  Code  |      Input      |
+--------+-----------------+
| 151821 | 687 235 563     |
| 123521 | 657 089 568 798 |
+--------+-----------------+

What I want to be able to do is split the "Input" string on the space character in a SQL view (which will then populate an excel query) to create something like this:

+--------+-------+
|  Code  | Input |
+--------+-------+
| 151821 |   687 |
| 151821 |   235 |
| 151821 |   563 |
| 123521 |   657 |
| 123521 |   089 |
| 123521 |   568 |
| 123521 |   798 |
+--------+-------+

I know I could do it at Excel level with a bit of VBA using the SPLIT function on the spaces, but was hoping there may well be a way to do it in SQL as well.

Downvote reason: a simple google search on 'split strings sql server' would give you [this great article](http://sqlperformance.com/2012/07/t-sql-queries/split-strings) — Zohar Peled, Jun 18 '15 at 08:30
Fair enough @ZoharPeled .I was googling a little more crazy than that: `sql make entry span multiple lines breaking on a character` as that made sense in my head. It's early and I need coffee ;) — bmgh1985, Jun 18 '15 at 08:42

score 0 · Answer 1 · answered Jun 18 '15 at 08:30

0

You'll probably have to create an SQL FUNCTION or something similar, as there aren't really any built in native SQL methods to do this.

answered Jun 18 '15 at 08:30

Hysteria86

367
1
10

score 0 · Accepted Answer · answered Jun 18 '15 at 08:31

I found this on the interwebs and have been using it for months to pass a comma separated value as a string and split it into records. Hopefully you can rework it or do something similar.

CREATE FUNCTION [dbo].[fn_MVParam_Split] (@RepParam nvarchar(4000), @Delim1 char(1)= ',', @Delim2 char(1)= '|') 
RETURNS @Values TABLE 
(
Param1 nvarchar(4000),
Param2 nvarchar(4000)
)
AS

BEGIN

DECLARE @chrind INT
DECLARE @chrind2 INT
DECLARE @Piece nvarchar(100) 
DECLARE @PieceLeft nvarchar(100) 
DECLARE @PieceRight nvarchar(100) 

SELECT @chrind = 1 
WHILE @chrind > 0 
BEGIN

  SELECT @chrind = CHARINDEX(@Delim1,@RepParam) 
  IF @chrind  > 0 
    SELECT @Piece = LEFT(@RepParam,@chrind - 1) 
  ELSE

    SELECT @Piece = @RepParam 

  SELECT @chrind2 = CHARINDEX(@Delim2,@Piece)

  IF @chrind2  > 0 
  BEGIN
    SELECT @PieceLeft = LEFT(@Piece,@chrind2 - 1)       
    SELECT @PieceRight = RIGHT(@Piece,LEN(@Piece) - @chrind2)       
  END
  ELSE
  BEGIN
    SELECT @PieceLeft = @Piece
    SELECT @PieceRight = ''
  END

  INSERT  @Values(Param1, Param2) VALUES(CAST(@PieceLeft AS VARCHAR), CAST(@PieceRight AS VARCHAR)) 
  SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind) 
  IF LEN(@RepParam) = 0 BREAK

END

RETURN

END

Split string to substrings on a character and turn into multiple lines SQL

2 Answers2