Remove specific quote marks from string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Remove specific quote marks from string

Author

Topic

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2008-09-20 : 13:15:37

Hi gang,
Here is an interesting one:
If we have a string that has several quote marks in it, and only want to remove certain ones depending on what come before it, how could it be done? For Example:

(((AR_CUST.CUST_NO >= '943') and (AR_CUST.CUST_NO <= '949'))) and (AR_CUST.ADRS_1 = 'WATKINS AVENUE')))

If we wanted to remove the quote marks from around the strings that are numeric, how would you do that? And just to make things interesting, suppose it is not always in the same order, like:

](((AR_CUST.ADRS_1 = 'WATKINS AVENUE') and (AR_CUST.CUST_NO >='943'))) and (AR_CUST.CUST_NO <= 949')))

Using replace is easy enough to remove ALL the quote marks, but...

There's never enough time to type code right,
but always enough time for a hotfix...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-22 : 16:40:21

There might be some cool way to do this, but the only way I know how to do something like this is a write a UDF and do string manipulation via a loop. :(

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2008-09-23 : 11:26:42

Heylampry,
Yeah, I've been playing around with a udf that removes only the data portion of the string and returns it delimited (The example I posted would be returned as 943|949|WATKINS), but what I'm looking to do is take something like the AR_CUST.CUST_NO string and change it so it is passed as a number instead of a string. This is one department in the company that is still using an older App, and it will not be changed for some time. What happens is when they try to retrieve a range of customers it returns unwanted records because of the string matching. Creating a view and casting the CUST_NO as numeric allows for ABS ordering and retieval, but the app needs to pass in a number to make it work...

Andy

There's never enough time to type code right,
but always enough time for a hotfix...

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2008-09-26 : 12:44:17

Ok,
Got it figured out, so I will post it here in case someone else has a use for it:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE      function fnStripQuotesFromValue(
	@FilterText	varchar(1000),	--string passed to function to be parsed
	@TB		varchar(100),	--Table name to look for
	@Col		varchar(100)	--Column name to look for
) returns varchar(1000)
as
begin
declare
	@NumVal		varchar(50),	--data string being converted to number
	@TB1		varchar(500),	--Concatenated table/column/expression string
	@TBel		int,		--indexed end of @TB1
	@TBql		int,		--indexed first quote mark
	@TB2ql		int,		--indexed second quote mark		
	@Rsc		int,		--Length of string after second quote mark
	@ExpStr		varchar(500),	--Comma delimited string of expressions
	@Exp		varchar(10),	--Each individual expression parsed from @ExpStr
	@Datavalue	varchar(1000)	--Output string to return
set @ExpStr ='=, =,= , = ,>, >,> , > ,<, <,< , < ,<>, <>,<> , <> ,>=, >=,>= , >= ,<=, <=,<= , <= ,' --Must have a delimiter at end;checks for spaces added or not before and after the expression
-- convert 2 single quotes to 1 quote before processing string
while charindex('''''', @FilterText, 1) <> 0
begin
  set @FilterText = replace(@FilterText, '''''', '''')
end
--------------------------------------------------------------
-- build the string
--------------------------------------------------------------
declare
@Expcnt int,	--Count of expressions to loop through
@TBei int	--Count of instances of table/column/expression found
set @Expcnt =  patindex('%,%' , @ExpStr)
while @Expcnt <> 0 
begin
 set @Exp = left(@ExpStr, @Expcnt - 1)
 set @TB1= @TB+'.'+@Col+@Exp+''''
 set @TBei = (LEN(REPLACE(@FilterText, @TB1, @TB1+@TB1)) - LEN(@FilterText))/LEN(@TB1)
	while @TBei>0
    begin
	 set @TBel = (charindex(@TB1,@FilterText,1)+(len(@TB)+1)+len(@col)+len(@Exp))	--End of first string
	 set @TBql = charindex('''', @FilterText, @TBel)				--First quote mark location
	 set @TB2ql = charindex('''', @FilterText, @TBql+1)				--Second quote mark location
	 set @Rsc = (len(@FilterText)-charindex('''', @FilterText, @TB2ql))		--Length of string after second quote mark
	 set @NumVal = substring(@FilterText,@TBql+1,(@TB2ql-@TBql-1))			--String value to remove quote marks from
	 set @FilterText = left(@FilterText, @TBel)+ @NumVal+right(@FilterText,@Rsc)	--Reassembled string without quote marks
	 set @TBei =@TBei-1								--Lower counter by 1 for loop
    end
 set @ExpStr = stuff(@ExpStr, 1, @Expcnt, '')	--Remove used expression from expression string
 set @Expcnt =  patindex('%,%' , @ExpStr)	--Reset the count of expressions to loop through
end
set @Datavalue = @FilterText			--Set output string value
return @DataValue
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Pass the function the whole string,table name and column name to remove the quotes from, and it parses out the string with the neccessary quotes removed.

There's never enough time to type code right,
but always enough time for a hotfix...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-26 : 14:18:13

[code]
Just as an alternative - it may be no more efficient just looks simpler.
This solution will not work unless the following assumptions are true:
a. Values to remove quotes from begins with either ">= '" or "<= '"
b. That the next quote (following assumption a.) is the ending quote

declare @s varchar(1000)
set @s = '(((AR_CUST.CUST_NO >= ''943'') and (AR_CUST.CUST_NO <= ''949''))) and (AR_CUST.ADRS_1 = ''WATKINS AVENUE'')))'

while patindex('%[<>]= ''%', @s) > 0
begin
select @s = stuff(stuff(@s, i2, 1, ''), i1, 1, '')
from (
select patindex('%[<>]= ''%', @s) + 3 i1
,charindex('''', @s, patindex('%[<>]= ''%', @s) + 4) i2
) d
end

select @s

output:
(((AR_CUST.CUST_NO >= 943) and (AR_CUST.CUST_NO <= 949))) and (AR_CUST.ADRS_1 = 'WATKINS AVENUE')))
[/code]

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources