Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

replacing multiple characters in a column Expand / Collapse
Author
Message
Posted Monday, January 28, 2008 10:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 07, 2011 4:48 PM
Points: 9, Visits: 77
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....
Post #448681
Posted Wednesday, January 30, 2008 8:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 01, 2010 10:16 AM
Points: 29, Visits: 62
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
Post #449472
Posted Wednesday, January 30, 2008 9:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:44 PM
Points: 7,084, Visits: 14,684
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?
Post #449478
Posted Wednesday, January 30, 2008 9:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 01, 2010 10:16 AM
Points: 29, Visits: 62
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.
Post #449488
Posted Thursday, January 31, 2008 8:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 12,744, Visits: 31,071
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:

    
--===== 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





Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #450002
Posted Thursday, January 31, 2008 5:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 07, 2011 4:48 PM
Points: 9, Visits: 77
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.
Post #450246
Posted Thursday, January 31, 2008 5:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 12,744, Visits: 31,071
search for "DBA Toolkit" hereon SSC:
www.sqlservercentral.com/articles/Security/sql2000dbatoolkitpart1/2361/

It's got encryption/decryption, regular expressions and more.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #450250
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse