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