Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


replacing multiple characters in a column


replacing multiple characters in a column

Author
Message
ranjithkumar-479831
ranjithkumar-479831
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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....
carnaud
carnaud
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
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
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7624 Visits: 18043
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?
carnaud
carnaud
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
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.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14910 Visits: 38896
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ranjithkumar-479831
ranjithkumar-479831
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14910 Visits: 38896
search for "DBA Toolkit" hereon SSC:
www.sqlservercentral.com/articles/Security/sql2000dbatoolkitpart1/2361/

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

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search