Search Database and return characters

Hi There,
Can you help please.
I have a table with fields that are key | value pairs.
I need to search the value field for a value and return a value between 2 characters.
So this is an example value "a:1:{s:15:"introducer_code";s:4:"6969";}" I need to search for "introducer_code" and when found return the value "6969". This value can be any number so it might be a "1" or "16784" or anything so I need to be able to return the value between :" ";}" from the value shown above.
So I'm thinking it will be something using instring(len(Value)) or something like that.

Thanks for the help.

Best Regards,
Steve.

Is this for microsoft sql server or other datastore like MongoDb?

hi i tried to do this

please let me know if it works
hope it helps
:slight_smile:
:slight_smile:

drop create data ...
create table data
(
 col1 varchar(100)
 )
 go

/******************************************************************************/
-- insert data tables 
insert into data select 'a:1:{s:15:"introducer_code";s:4:"6969";}'
insert into data select 'a:1:{s:15:"introducer_code";s:4:"1";}'
insert into data select 'a:1:{s:15:"introducer_code";s:4:"16784";}'
insert into data select 'a:1:{s:15:"xxx";s:4:"6969";}'
insert into data select 'a:1:{s:15:"xxx";s:4:"1";}'
insert into data select 'a:1:{s:15:"xxx";s:4:"16784";}'
go
SQL ...
select replace(
substring (col1,
CHARINDEX('"', col1,CHARINDEX('"', col1, CHARINDEX('"', col1, 1) + 1)+1),
len(col1) - CHARINDEX('"', col1,CHARINDEX('"', col1, CHARINDEX('"', col1, 1) + 1)+1)-1)
, '"','')
from data
where col1 like '%introducer_code%'
go
Results

image

hi

Another way of doing this
using tally cte

its a bit more code
its dynamic
but will work for strings which are very long

:slight_smile:
:slight_smile:

Much better way of doing then my previous solution

create data ..
create table data
(
 col1 varchar(100)
 )
 go

/******************************************************************************/
-- insert data tables 
insert into data select 'a:1:{s:15:"introducer_code";s:4:"6969";}'
insert into data select 'a:1:{s:15:"introducer_code";s:4:"1";}'
insert into data select 'a:1:{s:15:"introducer_code";s:4:"16784";}'
insert into data select 'a:1:{s:15:"xxx";s:4:"6969";}'
insert into data select 'a:1:{s:15:"xxx";s:4:"1";}'
insert into data select 'a:1:{s:15:"xxx";s:4:"16784";}'
go
SQL using tally cte ...
;WITH tally_cte 
     AS (SELECT N=1 
         UNION ALL 
         SELECT n + 1 
         FROM   tally_cte 
         WHERE  n + 1 < 20), 
     data_filter 
     AS (SELECT col1 
         FROM   data 
         WHERE  col1 LIKE '%introducer_code%'), 
     rec_cte (col1, starts, pos) 
     AS (SELECT col1, 
                1, 
                Charindex('"', col1) 
         FROM   data_filter 
         UNION ALL 
         SELECT col1, 
                pos + 1, 
                Charindex('"', col1, pos + 1) 
         FROM   rec_cte 
         WHERE  pos > 0), 
     rn_cte 
     AS (SELECT Row_number() 
                  OVER( 
                    partition BY col1 
                    ORDER BY pos)            AS rn, 
                col1, 
                Substring(col1, starts, CASE 
                                          WHEN pos > 0 THEN pos - starts 
                                          ELSE Len(col1) 
                                        END) token 
         FROM   rec_cte) 
SELECT * 
FROM   rn_cte 
WHERE  rn = 5 

go
Results ..

image

using the split function DelimitedSplit8K

use sqlteam
go

create table #steve_sample(introduction varchar(max) )

insert into #steve_sample
select 'a:1:{s:15:"introducer_code";s:4:"6969";}' union
select 'a:2:{s:16:"introducer_code";s:5:"6970";}' union
select 'a:4:{s:17:"introducer_code";s:6:"6971";}' 

select replace(REPLACE(Item, ';}', ''),'"','')
 From (
	select * 
	  From #steve_sample s
	  CROSS APPLY dbo.DelimitedSplit8K(s.introduction, N':') d2
) a 
where Item like '%;}'
drop table #steve_sample

image