Removing spaces from a string

• sqlrumble

  Default port

  Points: 1458

  More actions

  November 11, 2004 at 4:20 am

  #61368

  Hello,

  I need to remove spaces,commas,full stops etc from a string in a column.

  For example if I have a column name called company name with the following value:

   - 'The, company. Name*was this'

  I would like to see 'thecompanynamewas'

  Can anybody provide any assistance?

  Thanks very much.

• Jay-81113

  Newbie

  Points: 1

  More actions

  November 11, 2004 at 4:41 am

  #529753

  select replace(replace(replace(replace('The, company. Name*was this',',',''),' ',''),'.',''),'*','')

  Regards,
  Jay.

• AJ Ahrens

  SSC-Insane

  Points: 20676

  More actions

  November 11, 2004 at 6:19 am

  #529764

  You might be able to use this and build a function out of it.  This will remove anything that is not from a-Z.

  SET NOCOUNT ON

  DECLARE @String AS VARCHAR(255)

  DECLARE @IntStr AS VARCHAR(255)

  DECLARE @Space AS VARCHAR(1)

  DECLARE @Loop   AS INTEGER

  DECLARE @i  AS INTEGER

  SET  @String = 'The, company. Name*was this'

  SET  @Loop = LEN(@String)

  SET  @i  = 1

  SET  @IntStr = ''

  WHILE @i <= @Loop

    BEGIN

   SET @Space = SUBSTRING(@String, @i, 1)

   IF ASCII(@Space) BETWEEN 65 AND 122

     BEGIN

    SET @IntStr = @IntStr + @Space

     END

   SET @i = @i + 1

  END

  SELECT @String, @IntStr

  SET NOCOUNT OFF

   

  
  
  Good Hunting!

  AJ Ahrens
  
  
  webmaster@kritter.net

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  November 12, 2004 at 12:30 am

  #529882

  I bet you'll already find something like this here in the script section.

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• sqlrumble

  Default port

  Points: 1458

  More actions

  November 23, 2004 at 3:38 am

  #531191

  Thanks

   

  I will give it a go.