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
johnnyg123Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jan LouwerensSoftware EngineerCommented:
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.
0
Mark WillsTopic AdvisorCommented:
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

0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try something like this:

declare @sql varchar(1000)
declare @startStr varchar(100)
declare @endStr varchar(100)
declare @findStr varchar(100)
declare @replaceStr varchar(100)

set @startStr = 'Select'
set @endStr = ','

--#test 1
print '# Test 1'

set @sql = 'Select orderid, orderamount,orderdate from orders where orderamount > 0'
print @sql

if charindex(',',@sql) > 0 and charindex(',',@sql) < charindex('from',@sql)
	begin
		set @findStr = substring(@sql, charindex('Select', @sql)+ len('Select')+1 , charindex(' from',@sql) - len('Select')-1-len(' '))
		set @replaceStr = substring(@sql, charindex('Select', @sql)+ len('Select')+1 , charindex(',',@sql) - len('Select') - Len(',')-1)+' '
		set @sql = replace(@sql, @findStr, @replaceStr); 
	end

print @sql


--#test 2
print '# Test 2'

set @sql = 'Select orderid, orderamount,orderdate,customerid from orderhistory where orderdate = ''1/1/2017'''
print @sql

if charindex(',',@sql) > 0 and charindex(',',@sql) < charindex('from',@sql)
	begin
		set @findStr = substring(@sql, charindex('Select', @sql)+ len('Select')+1 , charindex(' from',@sql) - len('Select')-1-len(' '))
		set @replaceStr = substring(@sql, charindex('Select', @sql)+ len('Select')+1 , charindex(',',@sql) - len('Select') - Len(',')-1)+' '
		set @sql = replace(@sql, @findStr, @replaceStr); 
	end

print @sql


--#test 3
print '# Test 3'

set @sql = 'Select orderid from orderhistory where orderamount > 0 and orderdate = ''1/1/2017'''
print @sql

if charindex(',',@sql) > 0 and charindex(',',@sql) < charindex('from',@sql)
	begin
		set @findStr = substring(@sql, charindex('Select', @sql)+ len('Select')+1 , charindex(' from',@sql) - len('Select')-1-len(' '))
		set @replaceStr = substring(@sql, charindex('Select', @sql)+ len('Select')+1 , charindex(',',@sql) - len('Select') - Len(',')-1)+' '
		set @sql = replace(@sql, @findStr, @replaceStr); 
	end

print @sql

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnnyg123Author Commented:
Perfect...Thanks
0
Mark WillsTopic AdvisorCommented:
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 :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.