January 28, 2008 at 10:40 pm
Hi, please help me.... I couldn't find answer for this anywhere...
I have a table with a column having data type varchar and it has data with numbers and alphabets in it. I want to replace the data in the column with ' ' where there are alphabets.
(REPLACE((column),'S','')) -- I know this would replace s with ' ', but how to replace all the alphabets with ' '.
Please answer, I urgently need the answer.
Thanks to all in advance....
January 30, 2008 at 8:59 am
I ran into this a while back. I came up with a solution, but it is not the best. Create a function in the database that takes one argument (value). It returns a stripped value that removes all non-numeric characters from a varchar string.
USE:
Update [Table] Set [Column] = dbo.fn_StripNONNumeric([Column]) WHERE ISNUMERIC([Column]) = 0
CREATE FUNCTION [dbo].[fn_StripNONNumeric] (@Value_In varchar(250))
Returns varchar(250)
AS
BEGIN
DECLARE @sChar CHAR(1), @Value_Out_stripped VARCHAR(250)
DECLARE@iStrLen bigint , @iStrPos bigint
--init variables
SELECT @iStrPos = 1
SELECT @Value_Out_stripped = ''
--get the string length
SELECT @iStrLen = LEN(@Value_In)
--loop through the set
WHILE @iStrPos <= @iStrLen
BEGIN
--get each character
SELECT @sChar = SUBSTRING(@Value_In,@iStrPos,1)
--make sure its between 0-9, A-Z, or a-z
IF ASCII(@sChar) >= 48 AND ASCII(@sChar) <= 57
SELECT @Value_Out_stripped = @Value_Out_stripped + @sChar
--increament counter
SELECT @iStrPos = @iStrPos + 1
END
RETURN @Value_Out_stripped
END
January 30, 2008 at 9:04 am
Also- if you're not averse to extended stored procedures - you can download the regular expressions support for SQL Server 2000 from the scripts area.
Would make this fairly easy....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 30, 2008 at 9:12 am
I would tend to agree. If you know how to use regular expressions, that would probably be a better solution. User defined functions tend to slow things down a lot. If you are using my method against large data sets it is going to have performance issues.
January 31, 2008 at 8:49 am
I've got a collection of functions like that that strip out certain characters, html encode data, etc, all based on Jeff Moden's Tally Table concept.
Try this out for size: it's really fast:
[font="Courier New"]
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM MASTER.dbo.SysColumns sc1,
MASTER.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
GO
CREATE FUNCTION StripNonNumeric(@OriginalText VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
DECLARE @CleanedText VARCHAR(8000)
SELECT @CleanedText = ISNULL(@CleanedText,'') +
CASE
--ascii numbers are 48(for '0') thru 57 (for '9')
WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 48
AND 57 THEN SUBSTRING(@OriginalText,Tally.N,1)
ELSE '' END
FROM dbo.Tally
WHERE Tally.N <= LEN(@OriginalText)
RETURN @CleanedText
END
GO
SELECT dbo.StripNonNumeric('Alex is 25 years old on 01/14/2008')
--results 2501142008[/font]
Lowell
January 31, 2008 at 5:32 pm
Thanks to everyone who have replied.
Where Can I get extended proc's for Reg expr's? They are not on the server I am using.
I am using sql server 2000.
Thanks.
January 31, 2008 at 5:42 pm
search for "DBA Toolkit" hereon SSC:
http://www.sqlservercentral.com/articles/Security/sql2000dbatoolkitpart1/2361/
It's got encryption/decryption, regular expressions and more.
Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply