Striping out text within a double quote MS SQL

emailamos
emailamos used Ask the Experts™
on
I'm trying to strip out text (+BAT_MATFXOPT+BAT_MATUPD) from one of the row   MultiFilterNames:"+BAT_MATFXOPT+BAT_MATUPD" DelivMonth:(|TF_DTRNG| ()) FXValueDate:(|TF_DTRNG| ()) FilterAttributesXML:"<FilterAttributes></FilterAttributes>" ) within double quote right after MultiFilterNames:


Thanks



MultiFilterNames.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can use the REPLACE function

SELECT REPLACE('MultiFilterNames:"+BAT_MATFXOPT+BAT_MATUPD" DelivMonth:(|TF_DTRNG| ()) FXValueDate:(|TF_DTRNG| ()) FilterAttributesXML:"<FilterAttributes></FilterAttributes>" )', '+BAT_MATFXOPT+BAT_MATUPD','')

Author

Commented:
Have a look at the attachment, it doesn't always return in that format after the quote.

Commented:
try the below:
just replace yourcol, with your actual column name.

select
	stuff(	
		yourcol, 
		charindex(yourcol, '"'),
		charindex(yourcol, '"', charindex(yourcol, '"') + 1),
		''
		)
from yourtable

Open in new window

Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
or you can actually simplify it a bit considering that all rows start with "MultiFilterNames:"

select
	stuff(	
		yourcol, 
		18,
		charindex(yourcol, '"', 19),
		''
		)
from yourtable

Open in new window

Commented:
Create the following function and use it for your field

CREATE function dbo.CustomFunc(@In varchar(8000)) returns varchar(8000)
begin
      declare @Out varchar(8000), @nTemp int
      set @nTemp=0
      set @Out=@In
      set @nTemp = charindex('"',@In)
      if (@nTemp>0)
      begin
            set @Out=substring(@In,1,@nTemp-1)
            set @In=substring(@In,@nTemp+1,len(@In))
            set @nTemp = charindex('"',@In)
            if (@nTemp>0)
                  set @Out=@Out+substring(@In,@nTemp+1,len(@In))
            else
                  set @Out=@Out+@In
      end      
      return @Out
end

Commented:
try this after u create the function

SELECT dbo.CustomFunc('MultiFilterNames:"+BAT_MATFXOPT+BAT_MATUPD" DelivMonth:(|TF_DTRNG| ()) FXValueDate:(|TF_DTRNG| ()) FilterAttributesXML:"<FilterAttributes></FilterAttributes>" )')
Commented:
gbanik, with all due respect, why would you create a function when there's one already available in SQL (STUFF) like I've shown above, which is way better optimized than yours to perform this task?
 

Commented:
ralmada... u  r correct.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial