August 12, 2008 at 9:17 am
Hi all,
Is it possible to remove all punction from a given string?
I.E. I'm looking for something like a function that will strip out anything from a given string that isnt a-z or 0-9.
As an example 'Starsky & Hutch Ltd.' should be returned as 'Starsky Hutch Ltd'
Thanks,
Brett
August 12, 2008 at 9:51 am
I've not seen anything to do this, but I guess you could write a function that would take a string parameter and do a replace on all the charactes you wanted removed.
i.e select replace(@string, '&', '')
Ed Phillips
August 12, 2008 at 9:51 am
I have done something similar using PATINDEX and a while loop, try this code.
DECLARE @i int
DECLARE @String varchar(100)
SET @string ='Starsky & Hutch Ltd.'
SET @i = (SELECT PATINDEX('%[^a-z^0-9 ]%', @String))
WHILE (@i > 0)
BEGIN
SET @String = (SELECT REPLACE(@String, SUBSTRING(@String, @i, 1), ''))
SET @i = (SELECT PATINDEX('%[^a-z^0-9 ]%', @String))
END
select @string
August 12, 2008 at 10:02 am
This can be done by using a CTE (Common Table Expression). There was an article on SQL Server Central about a very similar topic yesterday actually. The code would look like this:
Declare
@Str1 VarChar(max),
@Str2 VarChar(max);
Select
@Str1='Starsky & Hutch Ltd.',
@Str2='';
With TextClean as
(
Select
Case
when SubString(@Str1,1,1) like '[a-z]' then SubString(@Str1,1,1)
else ''
End[Chr],
1[Idx]
Union All
Select
Case
when SubString(@Str1,Idx+1,1) like '[a-z]' then SubString(@Str1,Idx+1,1)
when SubString(@Str1,Idx+1,1) = ' ' then SubString(@Str1,Idx+1,1)
else ''
End,
Idx+1
from TextClean
where (Idx+1)<=Len(@Str1)
)
Select
@Str2=@Str2+Chr
from TextClean
option (MaxRecursion 0);
Select
@Str2;
August 12, 2008 at 10:35 am
using a numbers / tally table
--drop tally table
DROP TABLE dbo.Numbers;
GO
-- Now re-create it and fill it with sequential numbers starting at 1
SELECT TOP 100000 IDENTITY(INT,1,1) AS Number
INTO dbo.Numbers
FROM master.INFORMATION_SCHEMA.COLUMNS i1
CROSS JOIN master.INFORMATION_SCHEMA.COLUMNS i2
CROSS JOIN master.INFORMATION_SCHEMA.COLUMNS i3;
GO
-- Add a primary key/clustered index to the numbers table
ALTER TABLE dbo.Numbers
ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number);
GO
--create a function that strips out non letters / numbers
CREATE FUNCTION dbo.fnStripString ( @Text varchar(100) )
RETURNS varchar(100)
AS
BEGIN
DECLARE @Result varchar(100)
SET @Result = ''
IF @Text LIKE '%[^a-z^0-9]%'
BEGIN
SELECT
@Result = @Result + SUBSTRING(@Text,N.Number,1)
FROM dbo.Numbers N
WHERE N.Number <= LEN(@Text)
AND SUBSTRING(@Text,N.Number,1) LIKE '[0-9a-z]'
END
ELSE
BEGIN
SET @Result = @Text
END
RETURN @Result
END
GO
--test
SELECT dbo.fnStripString('remove!!')
SELECT dbo.fnStripString('duck & run')
SELECT dbo.fnStripString('@123 abc...')
GO
August 12, 2008 at 10:57 am
steveb (8/12/2008)
I have done something similar using PATINDEX and a while loop, try this code.
DECLARE @i int
DECLARE @String varchar(100)
SET @string ='Starsky & Hutch Ltd.'
SET @i = (SELECT PATINDEX('%[^a-z^0-9 ]%', @String))
WHILE (@i > 0)
BEGIN
SET @String = (SELECT REPLACE(@String, SUBSTRING(@String, @i, 1), ''))
SET @i = (SELECT PATINDEX('%[^a-z^0-9 ]%', @String))
END
select @string
This worked perfectly!!
Thanks a lot for your help all.
Regards,
Brett
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy