Repalce Function :Urgent help

emeka · February 19th, 2007, 04:02 AM

I have a table that looks like this

Table test:
Name num
tony 100,25
Dav 200.300
Emma 100 2,5.35

Then I have this statement:
Insert into Test2
select [name],Replace(Replace(Replace([num], ',', ''), '.',''), ' ', '')
From Test

The above code replaces those non- alphabetical charaters. My question is, is there any other way I can write the query to replace the non-Alphabetical characters like(!@#$%^&*()_+=-\|><,.?/)etc without nesting the Replace function.

Urgent help required.

happygv · February 19th, 2007, 06:21 AM

What does the value num column mean? Why is that you have numbers with other special characters like , and . used there? Why is that you want to replace it now?

How about explaining us on that?

Cheers

_________________________
- Vijay G
Strive for Perfection

Jeff Moden · February 19th, 2007, 09:10 AM

[Num] is the column name that Emeka is trying to "clean".

Emeka... this and similar functions will work. This one uses a "Tally" table to keep life simple. There are other functions that are somewhat faster, but you'll like this...

This creates the "Tally" table...

Code:

---------------------------------------------------------------------
--===== Create a tally table of numbers
 SELECT TOP 9999 IDENTITY(INT,1,1) AS N
   INTO dbo.Tally
   FROM dbo.SYSCOLUMNS sc1,
        dbo.SYSCOLUMNS sc2

--===== Give the tally table a clustered primary key for speed
  ALTER TABLE dbo.Tally
        ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)

--===== Grant SELECT privs to the public
  GRANT SELECT ON dbo.Tally TO PUBLIC
GO
---------------------------------------------------------------------

This creates the function you'll need...

Code:

---------------------------------------------------------------------
--===== Create a function to clean telephone and other numbers
 CREATE FUNCTION dbo.CleanNumber (@StringNumber VARCHAR(8000))
RETURNS VARCHAR(8000)
     AS
  BEGIN
        --===== Declare local variables
        DECLARE @NewString VARCHAR(8000)
            SET @NewString = ''

        --===== Strip out ALL non-digit characters
         SELECT @NewString = @NewString + SUBSTRING(@StringNumber,N,1)
           FROM dbo.Tally WITH (NOLOCK)
          WHERE N <= LEN(@StringNumber)
            AND SUBSTRING(@StringNumber,N,1) LIKE '%[0-9]%'
          ORDER BY N

        --===== Return the cleaned string (NULL if no numbers present)
         RETURN NULLIF(@NewString,'')
    END
GO
--===== Grant EXECUTE privs to the public
  GRANT EXECUTE ON dbo.CleanNumber TO PUBLIC
GO
---------------------------------------------------------------------

And this demonstrates the funtion with some test data that I put into a test table...

Code:

---------------------------------------------------------------------
--===== If the temporary test table exists, drop it
     IF OBJECT_ID('TempDB..#MyHead') IS NOT NULL
        DROP TABLE #MyHead

--===== Create a test table full of mixed numbers
SELECT '+1-310-414-0909' AS MixedNumber
  INTO #MyHead UNION ALL
SELECT 'k3j4j5h6h7h5kj4h4khk343&%@^)#*(&$%#%$!@12' UNION ALL
SELECT '^(%$%)(DHFHSDFHOD)3(&)(F^)YFGFYP(DFY_(DYFYF' UNION ALL
SELECT '1234567890' UNION ALL
SELECT 'ABCDEF' 

--===== Demo the function
 SELECT MixedNumber AS OriginalNumber,
        dbo.CleanNumber(MixedNumber) AS CleanedNumber
   FROM #MyHead
---------------------------------------------------------------------

If you don't like the "Tally" table, you can do something similar with a loop. Either way works well...

--Jeff Moden

emeka · February 20th, 2007, 03:44 AM

Thanks Jeff.
The code works perfectly.