SQL extracting specific values from a Varchar column & Splitting those column values into rows

Question

I have tried executing the below code & it throws error

Msg 2809, The request for procedure 'SplitStringTest' failed because 'SplitStringTest' is a table valued function object."

DECLARE @ID INT
DECLARE @Name VARCHAR(MAX)
DECLARE @opt VARCHAR(MAX)
DECLARE CursorTest CURSOR
   FOR SELECT ID, 
Name, 
dbo.RemoveCharacterswithoutspacesTest(Desc) as opt
FROM input_table
OPEN CursorTest

      FETCH NEXT FROM CursorTest
            INTO @ID,@Name, @opt
      WHILE @@FETCH_STATUS = 0
            BEGIN 
                  EXEC SplitStringTest @opt
            FETCH NEXT FROM CursorTest
                  INTO @ID, @Name, @opt
            END         


CLOSE CursorTest
DEALLOCATE CursorTest

I am looking for a solution in SQL Server as shown below

Current table:

ID    Name    Desc
-----------------------
123   KR      difference 76887 ghfr 88888063 7282
456    CD     088724   see there  29851  bus 0012

expected output:

ID    NAME    DESC
-----------------------
123    KR     76887
123    KR     88888063
123    KR     7282
456    CD     088724
456    CD     29851
456    CD     0012

Possible duplicate of [Splitting delimited values in a SQL column into multiple rows](https://stackoverflow.com/questions/11018076/splitting-delimited-values-in-a-sql-column-into-multiple-rows) — Jay Shankar Gupta, Mar 27 '18 at 17:05
i have tried creating two different functions, one to extract only numeric values from the column & other to split those values into rows. Both functions work when tested independently. but when tried to execute those together as a cursor it throws error Msg 2809 "The request for procedure 'proc_name' failed because 'function_name' is a table valued function object" — user7427808, Mar 27 '18 at 18:13

Jay Shankar Gupta · Answer 1 · 2018-03-28T13:36:23.650

0

SQL Server (starting with 2016)

SELECT ID, Name, value  
FROM Table1  
CROSS APPLY STRING_SPLIT([desc], ' ')
WHERE ISNUMERIC(value)=1;

Output

ID  Name  value
123 KR    76887
123 KR    88888063
123 KR    7282
456 CD    088724
456 CD    29851
456 CD    0012

Live Demo

http://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=f049720899f7a106c83ae7c02bfbde32

edited Mar 28 '18 at 13:36

answered Mar 27 '18 at 17:18

Jay Shankar Gupta

5,918
1
10
27

It returns **all** space-separated-values, while the OP expects numeric strings only, i.e. without "difference", "ghfr" etc. – Littlefoot Mar 27 '18 at 18:11
I did changes @Littlefoot – Jay Shankar Gupta Mar 28 '18 at 13:36

score 0 · Answer 2 · answered Mar 27 '18 at 18:09

As you tagged it with PL/SQL tag, which belongs to Oracle, here's one option:

SQL> with test (id, name, descr) as
  2    (select 123, 'KR', 'difference 76887 ghfr 88888063 7282' from dual union
  3     select 456, 'CD', '088724   see there  29851  bus 0012' from dual
  4    )
  5  select id, name,
  6    regexp_substr(descr, '\d+', 1, column_value) descr
  7  from test,
  8       table(cast(multiset(select level from dual
  9                           connect by level <= regexp_count(descr, ' ') + 1
 10                          ) as sys.odcinumberlist))
 11  where regexp_like(regexp_substr(descr, '\d+', 1, column_value), '\d+')
 12  order by id, name;

        ID NAME       DESCR
---------- ---------- ----------
       123 KR         76887
       123 KR         88888063
       123 KR         7282
       456 CD         088724
       456 CD         29851
       456 CD         0012

6 rows selected.

SQL>

SQL extracting specific values from a Varchar column & Splitting those column values into rows

2 Answers2