Stripping Out Quotes
-
Im trying to write a sql statement to strip out quotes. And Im having a difficult time with it.
Here is an example after the 4.
3-0X6-8 6PNL STEEL DOOR SLAB 2-3/4" BCKST W/REINFRCING PLTE
Does anyone have any ideas what functions I can use?
Thanks
Joe
-
can it be?:
--for stripping double qoutes:
update table_name set column_name = replace (column_name, '"', '')
-
Here is one approach. I imagine a number of people much smarter than I will either give you another direction or improve upon this.
Basically, we created a table called SpecialCharacters and put in the actual and replacement value (we use this for dynamic SQL when certain codes are pasted in from other sources) and included a description so everyone would be aware of why the choices were made.
Then, you simply use the function to replace those characters you encounter. We named it 2000 not for the year, but because we decide upon that size for fields. Hope this helps.
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id( N'dbo.SpecialCharacters') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.SpecialCharacters
GO
CREATE TABLE dbo.SpecialCharacters(
RowID integer IDENTITY (1, 1) NOT NULL,
SearchForCharacter varchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
ReplacementCharacter varchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
Explanation varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [cuidx_SearchForCharacter] ON dbo.SpecialCharacters( SearchForCharacter) ON [PRIMARY]
GO
CREATE INDEX [idx_RowID] ON dbo.SpecialCharacters( RowID) ON [PRIMARY]
GO
-------------------------------------------------------------------------------------
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id( N'dbo.ReplaceSpecialCharacter2000') AND xtype IN( N'FN', N'IF', N'TF'))
DROP FUNCTION dbo.ReplaceSpecialCharacter2000
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION dbo.ReplaceSpecialCharacter2000 ( @ValueToAlter AS varchar(2000)) RETURNS varchar(2000)
AS
BEGIN
DECLARE @CurrentID integer,
@MaxID integer,
@ValueToReplace varchar(1),
@ReplacementValue varchar(1)
SELECT @CurrentID = (SELECT MIN( RowID) FROM SpecialCharacters)
SELECT @MaxID = (SELECT MAX( RowID) FROM SpecialCharacters)
SELECT @ValueToReplace = (SELECT SearchForCharacter FROM SpecialCharacters WHERE RowID = @CurrentID)
SELECT @ReplacementValue = (SELECT ReplacementCharacter FROM SpecialCharacters WHERE RowID = @CurrentID)
WHILE @CurrentID <= @MaxID
BEGIN
SELECT @ValueToAlter = REPLACE( @ValueToAlter, @ValueToReplace, @ReplacementValue)
SELECT @CurrentID = (SELECT MIN( RowID) FROM SpecialCharacters WHERE RowID > @CurrentID)
SELECT @ValueToReplace = (SELECT SearchForCharacter FROM SpecialCharacters WHERE RowID = @CurrentID)
SELECT @ReplacementValue = (SELECT ReplacementCharacter FROM SpecialCharacters WHERE RowID = @CurrentID)
END
RETURN ( @ValueToAlter )
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I wasn't born stupid - I had to study.
-
That replaces " by a single space, unfortunately. Is there an easy way to have all quotes in a string replaced by what is known as "the empty string" in a traditional programming language?
-
Sorry - that was a reply to the previous post
-
In my case I just needed to replace the quote with a space.
The replace worked fine.
Thanks
-
to Jesper:
U wrote: That replaces " by a single space, unfortunately. Is there an easy way to have all quotes in a string replaced by what is known as "the empty string" in a traditional programming language?
just wanted to point out that
update table_name set column_name = replace (column_name, '"', '')
replaces an occurence of double quote with a "the empty string" (zero-length steing), not with a space as you said. '' means exactly that (not only in TSQL I think). Single space would be like ' '
-
Try this:
if '' = ' '
select 'equal'
else
select 'not equal'
-- returns 'equal'
select replace('a"a', '"', '')
-- returns 'a a'
String manipulations in T-SQL are tricky (not to say strange) - at least on my db....
-
got mixed results:
--the same as yours here:
if '' = ' '
select 'equal'
else
select 'not equal'
-- returns 'equal'
--that 's different from what u got:
select replace('a"a', '"', '')
-- returns 'aa'
I'm using MS SQL 2000
-
That is really odd, because if you try the following:
SELECT ASCII( '')
SELECT ASCII( ' ')
IF '' = ' '
SELECT 'equal'
ELSE
SELECT 'not equal'
-- returns 'equal'
SELECT REPLACE( 'a"a', '"', '')
-- returns 'aa' (mine does not return a space between the two a's)
SQL Server recognizes the difference between the two...
I wasn't born stupid - I had to study.
-
Sorry - it's because my db is on 65 compatibility level (enterprise manager, right-click on db, properties, options tab).
-
still cannot understand why
IF '' = ' '
SELECT 'equal'
ELSE
SELECT 'not equal'
--returns 'equal'
anyone have an idea?
-
Because it was equal in sql 6.5 (apparently).
-
they are not equal in MS SQL 2000. I tried this
select ascii('')
--returns NULL
select ascii(' ')
--returns 32
still:
IF '' = ' '
SELECT 'equal'
ELSE
SELECT 'not equal'
--returns 'equal'
?
-
Hey you asked for it
.IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id( N'dbo.ReplaceSpecialCharacter2000') and XType = 'FN')
DROP FUNCTION dbo.ReplaceSpecialCharacter2000
GO
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.SpecialCharacters') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.SpecialCharacters
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.SpecialCharacters(
RowID integer IDENTITY (1, 1) NOT NULL,
SearchForCharacter varchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
ReplacementCharacter varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
Explanation varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
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.SpecialCharacters (SearchForCharacter, ReplacementCharacter, Explanation) values ('''', '', 'Test')
Insert into dbo.SpecialCharacters (SearchForCharacter, ReplacementCharacter, Explanation) values ('"', '', 'Test')
Insert into dbo.SpecialCharacters (SearchForCharacter, ReplacementCharacter, Explanation) values (' ', '_', 'Test')
Insert into dbo.SpecialCharacters (SearchForCharacter, ReplacementCharacter, Explanation) values ('@', 'A', 'Test')
Insert into dbo.SpecialCharacters (SearchForCharacter, ReplacementCharacter, 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 (' ', '_', 'Test')
Insert into dbo.SpecialCharacters2 (Search, Replacement, Explanation) values ('@', 'A', 'Test')
Insert into dbo.SpecialCharacters2 (Search, Replacement, Explanation) values ('\', '\\', 'Test')
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id( N'dbo.ReplaceSpecialCharacter2000') AND xtype IN( N'FN', N'IF', N'TF'))
DROP FUNCTION dbo.ReplaceSpecialCharacter2000
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION dbo.ReplaceSpecialCharacter2000 ( @ValueToAlter AS varchar(2000)) RETURNS varchar(2000)
AS
BEGIN
DECLARE @CurrentID integer,
@MaxID integer,
@ValueToReplace varchar(1),
@ReplacementValue varchar(2)
SELECT @CurrentID = (SELECT MIN( RowID) FROM SpecialCharacters)
SELECT @MaxID = (SELECT MAX( RowID) FROM SpecialCharacters)
SELECT @ValueToReplace = (SELECT SearchForCharacter FROM SpecialCharacters WHERE RowID = @CurrentID)
SELECT @ReplacementValue = (SELECT ReplacementCharacter FROM SpecialCharacters WHERE RowID = @CurrentID)
WHILE @CurrentID @CurrentID)
SELECT @ValueToReplace = (SELECT SearchForCharacter FROM SpecialCharacters WHERE RowID = @CurrentID)
SELECT @ReplacementValue = (SELECT ReplacementCharacter FROM SpecialCharacters WHERE RowID = @CurrentID)
END
RETURN ( @ValueToAlter )
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.ReplaceSpecialCharacter2000_2 ( @ValueToAlter AS varchar(2000))
RETURNS varchar(2000)
AS
BEGIN
Select @ValueToAlter = Replace (@ValueToAlter, Search, Replacement) from dbo.SpecialCharacters2
RETURN ( @ValueToAlter )
END
GO
Select dbo.ReplaceSpecialCharacter2000 (replicate('f''gr"7 df@@8j\', 130))
Go
--fgr7_dfAA8j\
GO
Select dbo.ReplaceSpecialCharacter2000_2 (replicate('f''gr"7 df@@8j\', 130))
--fgr7_dfAA8j\
GO
DROP FUNCTION ReplaceSpecialCharacter2000
DROP FUNCTION ReplaceSpecialCharacter2000_2
DROP TABLE SpecialCharacters
DROP TABLE SpecialCharacters2
This version yields 12 times less read on the disk... didn't test for absolute perf difference but it's definitly much faster
.
Viewing 15 posts - 1 through 15 (of 48 total)
You must be logged in to reply to this topic. Login to reply