0

I have a string like this that doesn't have a consistent format and needs to be broken into fields by the ':' delimiter

  1:35410:102001001:102001:10:1

'STRING_SPLIT' is not a recognized built-in function name in my version of SQL Server

Any other suggestions? Thanks in advance!

jch
  • 187
  • 3
  • 17
  • did you try writing your own function? – Shekhar Reddy Apr 27 '18 at 18:57
  • What version of SQL Server are you using @jch? If you're using SQL Server 2008 you could go to this [link](https://stackoverflow.com/questions/10914576/t-sql-split-string) – whatthefish Apr 27 '18 at 19:14
  • take a peek at https://stackoverflow.com/questions/50063415/whats-the-most-efficient-way-to-normalize-text-from-column-into-a-table/50064225#50064225 – John Cappelletti Apr 27 '18 at 20:10
  • I had an XML solution but I can't find it. Hoping the internets could remind me.... – jch Apr 27 '18 at 20:14
  • Database tables Does not have "fields". they have columns and rows. What do you want to do with your string? convert it to different columns or to different rows? – Zohar Peled Apr 28 '18 at 04:24
  • The duplicate-link contains a lot of working answers. Hint: Sort them by `active`. Some of the high rated answers were good at their times, but are outdated... – Shnugo Apr 28 '18 at 11:18

1 Answers1

0

here is an example of a TVF that I use quite often. What it will do is take in a delimited string value and the value of the delimiter and return a table containing the split values. 

CREATE FUNCTION [dbo].[UDF_GetTableFromList]
(
    @LIST varchar(max), 
    @DELIMITER char(1)= ','
)

RETURNS @RETURN_TABLE TABLE (Param varchar(4000))

AS
BEGIN

Declare @POS int,
    @PIECE varchar(4000)

Set @LIST = ltrim(rtrim(@LIST)) + @DELIMITER
Set @POS = charindex(@DELIMITER, @LIST, 1)

-- parse the string into a table
if REPLACE(@LIST, @DELIMITER, '') <> ''
begin

WHILE @POS > 0
    begin

    SET @PIECE = LTRIM(RTRIM(LEFT(@LIST, @POS - 1)))
    IF @PIECE <> ''
        begin

        INSERT INTO @RETURN_TABLE (param) VALUES (CAST(@PIECE AS varchar(4000)))

        end

    SET @LIST = RIGHT(@LIST, LEN(@LIST) - @POS)
    SET @POS = CHARINDEX(@DELIMITER, @LIST, 1)

    END
End
RETURN
END
GO

execution example would be 

select * from [dbo].[UDF_GetTableFromList]('a,b,c,d',',')

and that would yield

return

in your particular case it would look something like this 

select * from [dbo].[UDF_GetTableFromList](' 1:35410:102001001:102001:10:1',':')

and yield the following

op example

SFrejofsky
  • 732
  • 5
  • 16
  • 1
    This is probably one of the worst ways you can split a string in SQL. That while loop RBAR approach is terrible. Read Aaron Bertrand's Split [strings the right way – or the next best way](https://sqlperformance.com/2012/07/t-sql-queries/split-strings) and change it to something better. – Zohar Peled Apr 28 '18 at 04:20
  • if object_id('tempdb..#init') is not null begin drop table #init end; SELECT PL.RECORD_ID, PL.PRINTER_LEVELS INTO #INIT FROM .... SELECT F1.RECORD_ID, F1.PRINTER_LEVELS, O.splitdata FROM ( SELECT *, cast(''+replace(F.PRINTER_LEVELS,':','')+'' as XML) as xmlfilter from #INIT F )F1 CROSS APPLY ( SELECT fdata.D.value('.','varchar(254)') as splitdata FROM f1.xmlfilter.nodes('X') as fdata(D)) O – jch Apr 30 '18 at 19:19