SQL Server - Split Function

Question

SQL Server - Split Function

Asked 6 years, 3 months ago

Viewed 2,008 times

2

I have the following table:

The goal is to make, in SQL Server, a function Split (by ',') the column EixoX where the TipoGrafico is different from 'Stockchart'. That is, if the TipoGrafico is different from 'Stockchart' that is where I should split the contents of the column EixoX.

Does anyone have an idea how to do?

Thanks in advance :)

You want to replace when TipoGrafico is equal to StockChart for the value of EixoX? That’s it?

– rbz

2018/07/27 at 10:27
@RBZ For example, when the Type is different from 'Stockchart', that’s where I should do the Split of the contents of the Eixox column :)

– Araújo

2018/07/27 at 10:30
Is "split" already working? If so, edit the question and put your query.

– rbz

2018/07/27 at 10:32
@RBZ No, this is because I still have to create the :/ function but to simplify I will leave the conditions to perform the Split: Select * from tbGraficoConsults Where Idconfigurationqueries = xxx and Typography <> 'Stockchart'

– Araújo

2018/07/27 at 10:34
what version of your bank?

– Thiago Magalhães

2018/07/27 at 10:38
@Thiagomagalhães is SQL Server 2014

– Araújo

2018/07/27 at 10:45
Try it that way: SELECT TipoGrafico, (CASE WHEN TipoGrafico <> 'StockChart' THEN STRING_SPLIT(EixoX, ',') ELSE EixoX END) EixoX_
FROM tabela

– rbz

2018/07/27 at 10:57
Or that: SELECT TipoGrafico, (CASE TipoGrafico WHEN 'StockChart' THEN EixoX 
 ELSE STRING_SPLIT(EixoX, ',') END) EixoX_ FROM tabela

– rbz

2018/07/27 at 11:01
@RBZ unfortunately cannot execute this query, this error appears: "'STRING_SPLIT' is not a recognized built-in Function name.". I think it must be because the 2014 version does not have this built-in function :/

– Araújo

2018/07/27 at 11:02
Yeah, you’re gonna have to create a function: Link1 , Link2

– rbz

2018/07/27 at 11:04
@RBZ Thanks for the help, I’ll try :)

– Araújo

2018/07/27 at 11:07
select will remain, changing only the STRING_SPLIT: SELECT TipoGrafico, (CASE TipoGrafico WHEN 'StockChart' THEN EixoX ELSE nomeFuncao(EixoX, ',') END) EixoX_ FROM tabela.

– rbz

2018/07/27 at 11:09
1

STRING_SPLIT is only available as of 2016 version of SQL Server, and the version that is being used according to the comment is 2014. There are several examples of functions to split on the internet, here one of them: how-to-split-comma-delimited-string

– Ricardo Pontual

2018/07/27 at 11:17
@Exact Ricardopunctual, unfortunately I am not able to use this function. Thanks for the help Ricardo :)

– Araújo

2018/07/27 at 11:25
@Araújo In the article "Separating multi-valued text content (split string)" you find several "split" tips. Acesse https://portosql.wordpress.com/2019/01/27/separar-conteo-texto-multivalorado_string-split/

– José Diz

2019/05/07 at 18:17

Show 10 more comments

1 answer

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.

by Marlon Araujo • 21 points · Answer 1 · 2018-07-27T12:07:49+00:00

I’ve got a job ready if I can work it out for you.

CREATE FUNCTION [dbo].[f_split] (@String NVARCHAR(4000), @Delimiter NCHAR(1)) RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)