asked on

grab sql string based on position of substring

I have a function that builds a sql string

its a long story but there are certain situations where I want to grab every thing after the word from and replace the columns selected with
'select orderid'

For example,

if the sql string is

Select orderid, orderamount,orderdate from orders where orderamount > 0

I want to end up with

Select orderid with from orders where orderamount > 0

Likewise,

if the sql string is

Select orderid, orderamount,orderdate,customerid from orderhistory where orderdate = '1/1/2017'

I would like to end up with

Select orderid from orderhistory where orderdate = '1/1/2017'

not sure the best way to do this

Jan Louwerens

It looks like you just want to replace everything between the first instances of "select " and " where ". (I added the whitespace around those keywords just in case there are fields that contain those words.)

Just get the indexes of the first occurrence of those strings, and use those to build your new query.

Mark Wills

Well identifying is easy enough... assuming column is 'string' and datasource is 'yourdata'

select string from yourdata where string like 'select orderid%from%'

identifying the From should also be easy enough (though might have a carriage return in your string)

select patindex('% from %',string) from yourdata

Then putting it all together...

select replace(string,substring(string,1,patindex('% from %',string)),'select orderid ') from yourdata where string like 'select orderid%from%'

Open in new window

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johnnyg123

ASKER

Perfect...Thanks

Mark Wills

Umm, did you even try my solution ? I know it looks simple, but it works - using the same strings as above...

select string = 'Select orderid, orderamount,orderdate,customerid from orderhistory where orderdate = ''1/1/2017''' into #tmp
insert #tmp values ('Select orderid, orderamount,orderdate from orders where orderamount > 0')
insert #tmp values ('Select orderid from orderhistory where orderamount > 0 and orderdate = ''1/1/2017''')

-- now the code 

select replace(string,substring(string,1,patindex('% from %',string)),'select orderid ') from #tmp where string like 'select orderid%from%'

Open in new window

It does work :)