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.
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
its a bit more code
its dynamic
but will work for strings which are very long
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
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