Return all results of substring with in string

Question

I have some odd data in a vendor database but need to be able to extract multiple different parameters from one field in the db.

So from this example i would like to pull out all items that fall between (" % ")

Between quotes is a string, disregard that it looks like code:

"Func_GetParameterLatestValue("IBW Patient Height RT Assess") kHeight =Func_GetParameterLatestValue("Height For IBW Vent Misc") If (kSex) = "" Then
Return_Value =NULL Else If kHeight > 0 Then If kSex=1 Then Return_Value= Round(((kHeight - 152.4)*.91)+50,0) Else
Return_Value= Round(((kHeight - 152.4)*.91)+45.5,0) End IF Else Return_Value = NULL End IF End IF ' Return_Value = kHeight '("IBW Patient Height RT Assess")"

so the return values would be:

IBW Patient Height RT Assess,
Height For IBW Vent Misc,
IBW Patient Height RT Assess

Im open to any suggestions to try and make this work. Ideally i would like to be able to slam the results in a subquery as well to make sure that they exist on another table.

This query currently returns the first instance

select vbs.Name, 
        SUBSTRING(sd.FormulaDetails, 
                  CHARINDEX('("', sd.FormulaDetails)+2,(CHARINDEX('")',sd.FormulaDetails) - CHARINDEX('("', sd.FormulaDetails))-2)
from StatementDefinitions sd, MvVBScript vbs
where sd.ScriptID = vbs.ID

You need to clarify this question. Are you asking how to substring the column results for some query? The above code doesnt even look like T-SQL. — Tejs
– Tejs, Commented Apr 27, 2012 at 17:43
sorry, it wasnt mean to look like code, its an actual piece of string text only.... — cjparker
– cjparker, Commented Apr 27, 2012 at 20:52
I'm more of a .NET guy than a SQL guy... so I'd lean towards a CLR-UDF — Sam Axe
– Sam Axe, Commented Apr 27, 2012 at 21:00

Steve Kass · Accepted Answer · 2012-04-28 02:35:40Z

2

You can do this recursively with a WITH statement. Here's a shot at it. Change varchar(max) to whatever the data type of your FormulaDetails column is. In case you want it, this query returns the ScriptID and numbers the position of the chunk it finds (so 'Height For IBW Vent Misc' would be occurrence 2)

with Chunks(id,occurrence,position,token,remainder) as (
  select
    ScriptID,
    cast(0 as int),
    charindex(@token,FormulaDetails),
    cast('' as varchar(max)),
    substring(c,charindex(@token,FormulaDetails)+1,len(FormulaDetails))
  from StatementDefinitions
  where FormulaDetails like '%'+@token+'%'
  union all
  select
    id,
    occurrence+1,
    charindex(@token,remainder)+position,
    cast(substring(remainder,1,charindex(@token,remainder)-1) as varchar(max)),
    substring(remainder,charindex(@token,remainder)+1,len(remainder))
  from Chunks
  where remainder like '%'+@token+'%'
)
  select id, occurrence, token from Chunks
  where occurrence > 0
  order by id;

answered Apr 28, 2012 at 2:35

Steve Kass

7,22422 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

cjparker Over a year ago

hey steve, thanks for this. What is 'C' in the first substring?

Steve Kass Over a year ago

Oops. c should be FormulaDetails, I'm pretty sure.

cjparker Over a year ago

thats what i thought but im getting zero results back :(

Steve Kass Over a year ago

Hi cjparker, Here's a SQL Fiddle for this: sqlfiddle.com/#!3/4817d/2 I fixed one mistake, but not one that would have prevented you from getting results. Hopefully this will help you use this idea.

cjparker Over a year ago

i totally must have mis entered something, seems to work pretty good after i used the query from sqlfiddle. thanks Steve!

Collectives™ on Stack Overflow

Return all results of substring with in string

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related