SUBSTRING_INDEX equivalent in SQL Server

Question

Table:

id | name    
1  | a,b,c    
2  | b

Output

id | name    
1  | a    
1  | b    
1  | c    
2  | b

I found this query online and it works fine in mySql Phpmyadmin query editor.

SELECT
  tablename.id, SUBSTRING_INDEX(SUBSTRING_INDEX (tablename.name, ',', numbers.n), ',', - 1) NAME
FROM  (SELECT 1 n UNION ALL
  SELECT 2 UNION ALL
  SELECT 3 UNION ALL
  SELECT 4) numbers
INNER JOIN tablename
    ON CHAR_LENGTH (tablename.name) - CHAR_LENGTH (REPLACE (tablename.name, ',', '')) >= numbers.n - 1
ORDER BY id, n

But in SQL Server, it throws

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]'SUBSTRING_INDEX' is not a recognized built-in function name.
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'numbers'.

Edit: This query uses in tableau development, Where TSQL is not working as expecting

enter image description here

Does this answer your question? [SQL Server equivalent of substring\_index function in MySQL](https://stackoverflow.com/questions/23854724/sql-server-equivalent-of-substring-index-function-in-mysql) — FanoFN, Apr 10 '20 at 06:07
Sorry.This is a different case though the heading is similar — Roopesh E, Apr 10 '20 at 06:12
MySQL and TSQL are different dialects of SQL Apparently you need to translate your MySQL query to TSQL - something you did not make apparent. But you would not have this problem with a properly normalized table - so maybe now is the time to fix your schema. — SMor, Apr 10 '20 at 11:42

score 0 · Answer 1 · answered Apr 10 '20 at 06:14

0

On SQL Server 2016 and later, we can use STRING_SPLIT here:

SELECT id, value AS name  
FROM yourTable
CROSS APPLY STRING_SPLIT(name, ',')
ORDER BY id, value;

answered Apr 10 '20 at 06:14

Tim Biegeleisen

502,043
27
286
360

I replaced SUBSTRNG_INDEX with CROSS APPLY STRING_SPLIT. but that doesnt works. it throews [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'CROSS'. [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'numbers'. – Roopesh E Apr 10 '20 at 06:38
1

What is your SQL Server version? Maybe it is time to upgrade. – Tim Biegeleisen Apr 10 '20 at 06:43

score 0 · Answer 2 · answered Apr 10 '20 at 06:20

0

Try this:

DECLARE @DataSource TABLE
(
    [ID] INT
   ,[name] VARCHAR(24)
);

INSERT INTO @DataSource ([ID], [Name])
VALUES (1, 'a,b,c')
      ,(2, 'b');

WITH DataSource ([ID], [nameXML]) AS
(
    SELECT [ID]
          ,CAST('<a>' + REPLACE([name], ',', '</a><a>')+ '</a>' AS XML)
    FROM @DataSource
)

SELECT [ID]
      ,T.c.value('.', 'VARCHAR(100)')
FROM DataSource DS
CROSS APPLY [nameXML].nodes('./a') T(c);

answered Apr 10 '20 at 06:20

gotqn

42,737
46
157
243

I wantee to execute the query in node express project with MSSQL database. So MSSQL query is required.. – Roopesh E Apr 10 '20 at 06:24
@RoopeshE It's `MSSQL` query, but better use `T-SQL` when referring the SQL dialect for Microsoft SQL Server. – gotqn Apr 10 '20 at 06:41
K. But could you provide the equivalent of this tsql? – Roopesh E Apr 10 '20 at 06:43
1

It is a valid T-SQL statement for sure. I have write it on my SSMS and execute it several times. – gotqn Apr 10 '20 at 06:44

score 0 · Answer 3 · answered Apr 10 '20 at 08:42

0

You could use a recursive CTE as the following

CREATE TABLE TBL(
  Id INT,
  String VARCHAR(45)
);

INSERT TBL VALUES
(1, 'a,b,c'),
(2, 'b');

WITH CTE(Id, Chr, String) AS
(
  SELECT Id,
         CAST(LEFT(String, CHARINDEX(',', String + ',') - 1) AS VARCHAR(20)),
         STUFF(String, 1, CHARINDEX(',', String + ','), '')
  FROM TBL
  UNION ALL
  SELECT Id,
         CAST(LEFT(String, CHARINDEX(',', String + ',') - 1) AS VARCHAR(20)),
         STUFF(String, 1, CHARINDEX(',', String + ','), '')
  FROM CTE
  WHERE String > ''
)
SELECT Id,
       Chr
FROM CTE
ORDER BY Id;

Online demo

answered Apr 10 '20 at 08:42

Ilyes

14,640
4
29
55

Thanks for your help. Bt I updated my question. The TSQL is not solution in this case. Because i need to put the querry in tableau development as well. There its not working. – Roopesh E Apr 10 '20 at 09:11
@RoopeshE So why you tag SQL Server in the first place? – Ilyes Apr 10 '20 at 09:15
Sorry for the mistake. i couldnt find mssql tag name. Also the database is accessing via sql server... And the usual query works in sql server. – Roopesh E Apr 10 '20 at 09:19

SUBSTRING_INDEX equivalent in SQL Server

3 Answers3