stringreplace

[aajay]

I need quick help
how to replace
(800) 223 4114 to 800 223 4114

Thanks

 

[PatriciaObreja]

SELECT REPLACE(REPLACE('(800) 223 4114', '(', ''), ')', '')

-----------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[aajay]

Thanks PatricaiObreja

 

[aajay]

HI

is there any way I can get rid of spaces in string

800 223 4114

instead


8002234114

thanks

 

[Qik3Coder]

i have a trim all function:

--Listing 1: UDF That Removes Extraneous Spaces from Char Columns
--copied from site on 21 NOV 2005
--modified to remove all spaces, David Fox 21 NOV 2005
--[URL unfurl="true"]http://www.windowsitpro.com/Files/09/37904/Listing_01.txt[/URL]

CREATE FUNCTION dbo.trimAll (@instring nvarchar(4000))
   RETURNS nvarchar(4000)
-- pmc 2002
-- UDF to strip leading, trailing, and repeating spaces
AS
BEGIN
   DECLARE @currentpos smallint
   DECLARE @space bit
   DECLARE @outstring nvarchar(4000), @workstring nvarchar(4000)
   DECLARE @currentchar nchar

   SET @currentpos=1                              -- Lets start at the beginning.
   SET @space=0x0                                     -- False (We didn't read a space last time.)
   SET @outstring=N''                                     -- Empty string to append results
   SET @workstring = LTRIM(RTRIM(@instring)) -- Free stripping

   IF @workstring IS NULL                               -- Cater for the NULL case.
      RETURN(null)

   WHILE @currentpos <= LEN(@workstring)                              -- For all chars
   BEGIN
      SET @currentchar=SUBSTRING(@workstring,@currentpos,1) -- Read a char.
      IF NOT(@currentchar = N' ')           -- OK if not space
      SET @outstring=@outstring + @currentchar

      IF @currentchar = N' '                               -- If it's a space, ignore the following spaces.
      SET @space = 0x1

    --ELSE                                                 -- Don't ignore the first space in a sequence.
    --SET @space = 0x0

      SET @currentpos = @currentpos+1                      -- And move on.
   END
--check result
--select @outstring

   RETURN(@outstring)                                      -- The result.
END

-Sometimes the answer to your question is the hack that works

 

[earthandfire]

Add another REPLACE

SELECT REPLACE(REPLACE(REPLACE('(800) 223 4114', '(', ''), ')', ''), ' ', '')

Hope this helps.

 

[aajay]

thanks
earthandfire it's working

 

[ESquared]

Qik3Coder:

CREATE FUNCTION dbo.trimAll (@instring nvarchar(4000))
   RETURNS nvarchar(4000)
AS
BEGIN
   SET @instring = LTrim(RTrim(@instring))
   WHILE @instring LIKE '%  %' SET @instring = Replace(@instring, '  ', ' ')
   RETURN @instring
END

 

[Qik3Coder]

Why, Thank you.
I had just copied it off the web when i had first started working as a dev.

-Sometimes the answer to your question is the hack that works