I have a strange request, I thikn. I have a system which I need to retrieve data from - its SQL based. The field in question has a multitude of data in it, but I specifically only need 1 or 2 values.
In the example below, the field contains values contained in "*". I need to retrieve the value for AQISPremIdProcessType_Hidden which is "N2725/D". Is this possible through a SQL script?
AQISEntityIds_Hidden=CN0001MB*AQISPremIdProcessType_Hidden=N2725/D*CPQuestionGenDate_Hidden=20/01/2017
I think that there may be a problem in your database design. Of course, I may be wrong but would there be a way to design a table with the following columns?
AQISEntityIds_Hidden | AQISPremIdProcessType_Hidden | CPQuestionGenDate_Hidden
How do you insert the values into your database? Do you use PHP?
Your string is twice delimited type with '*' and '=' as delimiters. So, if you use PHP, you could explode the string to get the desired values and store them in seperate fields in a table. That way, you could handle the values much easier (and this is the purpose of a database).
Again, I am sorry if the above is not possible in your project. I write this answer, just in case you could fix some things in your whole code approach and database design to make things work easier.
You can use charindex(<what>, <where>) function. First, to find index of AQISPremIdProcessType_Hidden= string. Second, to find index of first * after it.
Here an example:
declare
@testString nvarchar(max) = N'AQISEntityIds_Hidden=CN0001MB*AQISPremIdProcessType_Hidden=N2725/D*CPQuestionGenDate_Hidden=20/01/2017',
@field nvarchar(max) = N'AQISPremIdProcessType_Hidden=';
;with _rawInfo as(
select @testString as [Str]
)
, _trimmedStart as (
select
[Str] as [Origin],
-- check here the math: 2nd arg = length of the tail, so we take
-- whole length [StrLen] and subtract index of @field and its length
right([Str], [StrLen] - FieldInd - FieldLen + 1) as Tail
from _rawInfo
cross apply (
select
len([Str]) as [StrLen],
charindex(@field, [Str]) as FieldInd,
len(@field) as FieldLen
) x
), _result as (
select
[Origin],
Tail,
-- 2nd arg = length of the left cut, so we just take index of '*'
left(Tail, charindex(N'*', Tail) - 1) as [Value]
from _trimmedStart
)
select
*
from _result
UPD. And query adapted for your data:
;with _trimmedStart as (
select
jd.*,
right(AddInfo, AddInfoLen - FieldInd - FieldLen + 1) as AddInfoTail
from JobDeclaration jd
cross apply (
select
len(AddInfo) as AddInfoLen,
charindex(@field, AddInfo) as FieldInd,
len(@field) as FieldLen
) x
), _result as (
select
t.*,
left(AddInfoTail, charindex(N'*', AddInfoTail) - 1) as [AQISPremIdProcessType_Hidden]
from _trimmedStart t
)
select
*
from _result
