Selecting between quotes (") in SQL Server 2012

Question

I have a table holding IDs in one column and a string in the second column like below.

COLUMN01               COLUMN02
----------------------------------------------------------------------------------    
1                      abc"11444,12,13"efg"14,15"hij"16,17,18,19"opqr
2                      ahsdhg"21,22,23"ghshds"24,25"fgh"26,27,28,28"shgshsg
3                      xvd"3142,32,33"hty"34,35"okli"36,37,38,39"adfd

Now I want to have the following result

COLUMN01               COLUMN02
-----------------------------------------------------------    
1                      11444,12,13,14,15,16,17,18,19
2                      21,22,23,24,25,26,27,28,28
3                      3142,32,33,34,35,36,37,38,39

How can I do that?

Thanks so much

what type of database are you using? MySQL, SQL Server, etc. — ps2goat, Jul 11 '14 at 20:31
Here is a link that can help you http://stackoverflow.com/questions/24704629/how-to-select-specific-data-between-quotes — SerhatCan, Jul 11 '14 at 20:34
@SerhatC.. I/m the same guy, just reposting it as it was not posted properly. — user3617463, Jul 11 '14 at 20:39
@Andreas But the OP added commas between the numbers belonging to different sets. — user2989408, Jul 11 '14 at 20:39

score 0 · Answer 1 · edited May 23 '17 at 10:32

I know you want to do it using SQL. But ones I had nearly the same problem and getting this data to a string using a php or another language, than parsing is a way to do it. For example, you can use this kind of code after receiving the data into a string.

function clean($string) {
   $string = str_replace(' ', '-', $string); // Replaces all spaces with hyphens.
   $string = preg_replace('/[^A-Za-z0-9\-]/', '', $string); // Removes special chars.

   return preg_replace('/-+/', '-', $string); // Replaces multiple hyphens with single one.
}

For more information you might want to look at this post that I retrieved the function: Remove all special characters from a string

As I said this is an easy way to do it, I hope this could help.

score 0 · Accepted Answer · edited May 23 '17 at 12:34

Here is one way (maybe not the best, but it seems to work). I am NOT a SQL guru...

First, create this SQL Function. It came from: Extract numbers from a text in SQL Server

create function [dbo].[GetNumbersFromText](@String varchar(2000))
returns table as return
(
  with C as
  (
    select cast(substring(S.Value, S1.Pos, S2.L) as int) as Number,
           stuff(s.Value, 1, S1.Pos + S2.L, '') as Value
    from (select @String+' ') as S(Value)
      cross apply (select patindex('%[0-9]%', S.Value)) as S1(Pos)
      cross apply (select patindex('%[^0-9]%', stuff(S.Value, 1, S1.Pos, ''))) as S2(L)
    union all
    select cast(substring(S.Value, S1.Pos, S2.L) as int),
           stuff(S.Value, 1, S1.Pos + S2.L, '')
    from C as S
      cross apply (select patindex('%[0-9]%', S.Value)) as S1(Pos)
      cross apply (select patindex('%[^0-9]%', stuff(S.Value, 1, S1.Pos, ''))) as S2(L)
    where patindex('%[0-9]%', S.Value) > 0
  )
  select Number
  from C
)

Then, you can do something like this to get the results you were asking for. Note that I broke the query up into 3 parts for clarity. And, obviously, you don't need to declare the table variable and insert data into it.

DECLARE @tbl
TABLE (
    COLUMN01 int,
    COLUMN02 varchar(max)
)
INSERT INTO @tbl VALUES (1, 'abc"11444,12,13"efg"14,15"hij"16,17,18,19"opqr')
INSERT INTO @tbl VALUES (2, 'ahsdhg"21,22,23"ghshds"24,25"fgh"26,27,28,28"shgshsg')
INSERT INTO @tbl VALUES (3, 'xvd"3142,32,33"hty"34,35"okli"36,37,38,39"adfd')

SELECT COLUMN01, SUBSTRING(COLUMN02, 2, LEN(COLUMN02) - 1) as COLUMN02 FROM
(
    SELECT COLUMN01, REPLACE(COLUMN02, ' ', '') as COLUMN02 FROM
    (
        SELECT COLUMN01, (select ',' + number as 'data()' from dbo.GetNumbersFromText(Column02) for xml path('')) as COLUMN02 FROM @tbl
    ) t
) tt
GO

output:

COLUMN01   COLUMN02
1        11444,12,13,14,15,16,17,18,19
2        21,22,23,24,25,26,27,28,28
3        3142,32,33,34,35,36,37,38,39

Selecting between quotes (") in SQL Server 2012

2 Answers2