SQLTeam.com | Weblogs | Forums

Search Database and return characters


#1

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.


#2

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


#3

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


#4

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


#5

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