Stripping Out Quotes
-
Thanks Remi! Always glad to see my code improved upon.... I will look into your changes...
Still curious about the following:
SELECT ASCII( '')
SELECT ASCII( ' ')
IF '' = ' '
SELECT 'equal'
ELSE
SELECT 'not equal'
-- returns 'equal'
Any ideas why the IF returns these as equal?
I wasn't born stupid - I had to study.
-
Hi Guys n Gals,
Just to throw things out a little further.....
MSSQL 2000:-
--The middle " is a double quote - not two singles
if '"' = '" '
select 'equal'
else
select 'not equal'
-- returns 'equal'
--The middle " is two singles
if ' ''' = ' '' '
select 'equal'
else
select 'not equal'
-- returns 'equal'
--With either, if you increase the space before the middle pair it picks it up - but not after the middle pair......
Explanations please Remi......
Have fun
Steve
We need men who can dream of things that never were.
-
my colleague just said that there is an configuration option in SQL Server to tell it to strip all trailing white spaces before evaluation a string. He would not give me any more details.
on the other hand: while..
select ascii('')
--returns NULL
select ascii(' ')
--returns 32
\s* are ignored here:
select ascii('a')
--returns 97
select ascii('a ')
--returns 97
-
I can't find squat on this behavior. The only stuff I find is about DTS.
-
Remi,
When I tested your code it actually replicated the output 130 times. Plus, with a 2000 character input, and using this function in a standard SP, we would have to run it more than 130 times...
Not sure if you solution is what I would need. I will post my test (which is just like yours without my function and table input) and you can see if I messed something up...
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id( N'dbo.ReplaceSpecialCharacter2000_2') and XType = 'FN')
DROP FUNCTION dbo.ReplaceSpecialCharacter2000_2
GO
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id( N'dbo.SpecialCharacters2') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.SpecialCharacters2
GO
CREATE TABLE dbo.SpecialCharacters2(
Search char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL PRIMARY KEY CLUSTERED,
Replacement varchar(5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
Explanation varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]
GO
INSERT INTO dbo.SpecialCharacters2( Search, Replacement, Explanation) VALUES( '''', '', 'Test')
INSERT INTO dbo.SpecialCharacters2( Search, Replacement, Explanation) VALUES( '"', '', 'Test')
INSERT INTO dbo.SpecialCharacters2( Search, Replacement, Explanation) VALUES( ' ', '_', 'Test')
INSERT INTO dbo.SpecialCharacters2( Search, Replacement, Explanation) VALUES( '@', 'A', 'Test')
INSERT INTO dbo.SpecialCharacters2( Search, Replacement, Explanation) VALUES( '\', '\\', 'Test')
CREATE FUNCTION dbo.ReplaceSpecialCharacter2000_2( @ValueToAlter varchar(2000))
RETURNS varchar(2000)
AS
BEGIN
SELECT @ValueToAlter = REPLACE( @ValueToAlter, Search, Replacement) FROM dbo.SpecialCharacters2
RETURN ( @ValueToAlter )
END
GO
SELECT dbo.ReplaceSpecialCharacter2000_2( REPLICATE( 'f''gr"7 df@@8j\', 130))
GO
-- fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\fgr7_dfAA8j\\
SELECT 'f gr"7 df|8j\' + CHAR(96) + CHAR(145) + CHAR(146) + CHAR(180) + CHAR(184)
GO
-- f gr"7 df|8j\`‘’´¸
SELECT dbo.ReplaceSpecialCharacter2000( 'f gr"7 df|8j\' + CHAR(96) + CHAR(145) + CHAR(146) + CHAR(180) + CHAR(184) ) -- note tabs and odd single quotes are removed for mine. The table was already populated...
GO
-- f gr"7 df|8j\''',,
DROP FUNCTION ReplaceSpecialCharacter2000_2
DROP TABLE SpecialCharacters2
I wasn't born stupid - I had to study.
-
I was testing for performance.
Have closer look at the call :
Select dbo.fnName (REPLICATE( 'f''gr"7 df@@8j\', 130))
-
ooops... Thanks I wasn't born stupid - I had to study.
-
Remi, I'm disappointed you don't have a set-based solution!
This version replaces individual characters with one SELECT. It will only find single characters, but it could use a multi-character replacement value.
It uses the master..spt_values table to get numbers from 1 to 256, if your strings are longer you need to create a separate table for Numbers.
create table ReplaceChar(
c1 char(1) not null primary key clustered,
c2 varchar(1) null)
go
insert into ReplaceChar values ('"', '')
insert into ReplaceChar values ('(', '<')
insert into ReplaceChar values (')', '>')
go
declare @sIn varchar(256), @sOut varchar(256)
set @sIn = 'Original string (with replacable " chars)'
set @sOut = ''
select @sOut = @sOut + isnull(r.C2, s.C1)
from (
select number, substring(@sIn, number, 1) as C1
from master.dbo.spt_values
where type = 'P' and number between 1 and len(@sIn)
) s
left join ReplaceChar r on r.C1 = s.C1
order by number
print @sOut
-
What's procedural about this???
CREATE FUNCTION dbo.ReplaceSpecialCharacter2000_2( @ValueToAlter varchar(2000))
RETURNS varchar(2000)
AS
BEGIN
SELECT @ValueToAlter = REPLACE( @ValueToAlter, Search, Replacement) FROM dbo.SpecialCharacters2
RETURN ( @ValueToAlter )
END
GO
-
The d is ignored also :
select ascii('ad')
Looks like it returns the ascii of the first character only.
-
The only explanation I can find is that the server seems to do a rtrim by default when comparing strings. Anyone can confirm this?
-
Remi's solution is superior. I may need to change my posting name to "Bulldozer". I cannot seem to rid myself of that approach and depend upon using SQL Server's own functionality rather than keep re-inventing the wheel.
I just talked with our DBA and he made a great point as to why the other issue is returning "equal". Even though SELECT ASCII('') returns NULL, SQL Server is still reading this as an empty string. It cannot make an equality comparison between strings, (you would need to use LEN() to make a valid comparison). Hence, the result is unknown; in other words it is neither True nor False. It returns "equal" because the comparison is not valid.
In the words of Don Rumsfield: "As we know, there are known knowns. There are things we know we know. We also know there are known unknowns. That is to say we know there are some things we do not know. But there are also unknown unknowns, the ones we don’t know we don’t know” --- Donald Rumsfeld
I wasn't born stupid - I had to study.
-
Make a request to steve... I'm sure he can arrange that. In the meantime we'll make a pleasure of calling you bulldozer
.In the words of Don Rumsfield: "As we know, there are known knowns. There are things we know we know. We also know there are known unknowns. That is to say we know there are some things we do not know. But there are also unknown unknowns, the ones we don’t know we don’t know” --- Donald Rumsfeld
I'd call that election by confusion
. -
I'm gonna call in the "Big Guns", Mr. Smarty-pants! Sushila and NoelD!!! They are WAY better at giving you a hard time than I am...
Signed,
Bulldozer
I wasn't born stupid - I had to study.
-
Noeld never gave me hard times.., He's more helpfull than anything else. Sushila tends to be more chatty which is hardly giving me any hart times at all.
Later Bulldozer
.
Viewing 15 posts - 16 through 30 (of 48 total)
You must be logged in to reply to this topic. Login to reply