Split a long string into an array of shorter strings in SQL

Question

SQL query?

I have a table column with string of various length. Forexample abcd, abcde, fghijk

Now I want to get all distinct 4 long strings from this column.

Output is then:

abcd, bcde, fghi, ghij, hijk,

Thanks

This is very painful to do in T-SQL, due its lack of sophisticated string handling. Splitting strings by a separator conveniently was only added in 2016; splitting strings into characters still isn't implemented. I look forward to seeing what people come up with, but be advised this is something you almost certainly want to solve in a decent programming language outside the database. Almost no solution is going to properly scale if the strings get large. — Jeroen Mostert, Jan 25 '19 at 11:12
Are you saying you want a distinct list of all strings which are 4 characters in length? — aSystemOverload, Jan 25 '19 at 11:15
Is `abcd, abcde, fghijk` the value in a single row or three rows? — Martin Smith, Jan 25 '19 at 11:39
Look at into this https://stackoverflow.com/questions/3621494/the-most-elegant-way-to-generate-permutations-in-sql-server — Suraj Kumar, Jan 25 '19 at 11:51
yes, need a distinct list of all strings which are 4 characters in length. I have some 120,000 strings with varying length (from 5 to 200 characters long). — Khan, Jan 25 '19 at 15:10

score 3 · Answer 1 · answered Jan 25 '19 at 11:19

You can use a numbers table.

Below uses master..spt_values for demo purposes but you should create a dedicated one instead

WITH Nums
     AS (SELECT number
         FROM   master..spt_values
         WHERE  type = 'P'
                AND number BETWEEN 1 AND 1000)
SELECT DISTINCT SUBSTRING(YourCol, Number, 4)
FROM   (VALUES('abcd'),
              ('abcde'),
              ('fghijk'))YourTable(YourCol)
       JOIN Nums N
         ON Number BETWEEN 1 AND LEN(YourCol) - 3

Split a long string into an array of shorter strings in SQL

1 Answers1