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?
 
Ryan ChongCommented:
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.