• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

remove blank spaces in SQL 2012

I used to prepare an excel spreadsheet with find and replace for the following

comma , with space
blank spaces to 1 space only)
clear formats
=clean(cellname)

I am now taking this data directly into a table in sql so need to set up some routines that do the same thing to remove all commas, blank spaces, hidden characters from the various fields.

what would the syntax be?
could this be all done with one update statement?

field1                  field2

john,smith          john     smith    

becomes "john smith" in both cases
0
Chris Michalczuk
Asked:
Chris Michalczuk
  • 3
  • 3
  • 3
1 Solution
 
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

In SQL you can use the replace statement to replace "," with Space and you can also replace Space to remove them but you have to have different update statement for different characters.

-- To replace ',' with Single Space ' '
UPDATE TABLE
SET COL = REPLACE(COL ,',',' ')

-- To replace Double space '  ' with Single Space ' '
UPDATE TABLE
SET COL = REPLACE(COL ,'  ',' ')
0
 
Chris MichalczukConsultantAuthor Commented:
Is there any way to replace a variable number of spaces with just 1 space in one replace statement
some rows in a field might have 6 spaces while another has 2 so both rows need to only have 1 remaining?
0
 
Scott PletcherSenior DBACommented:
>> Is there any way to replace a variable number of spaces with just 1 space in one replace statement <<

Two methods, better-performing first:
SELECT string,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(string)),
SPACE(33), SPACE(1)),SPACE(17), SPACE(1)),SPACE(09), SPACE(1)),
SPACE(05), SPACE(1)),SPACE(03), SPACE(1)),SPACE(02), SPACE(1)),
SPACE(02), SPACE(1))
from (select 'ab   d  e f g   h  i                    j' as string) as x

SELECT string,
REPLACE(REPLACE(REPLACE(string,SPACE(2),SPACE(1)+CHAR(7)),CHAR(7)+SPACE(1),''),CHAR(7),'')
from (select 'ab   d  e f g   h  i                    j' as string) as x
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Scott PletcherSenior DBACommented:
Of course your preferred method could be converted into a function.

To get rid of other chars, you need to decide on all "good" characters.  Is it only a-z?  What about '.' (as in "Dr.")?  What about 3 (as in "Bill Smith III" or "Bill Smith 3rd")?

Once that's decided an efficient function can remove all "non-good" characters.
0
 
Chris MichalczukConsultantAuthor Commented:
tried this and it fails

 SELECT [ADDRESS_1]
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM([ADDRESS_1])),
SPACE(33), SPACE(1)),SPACE(17), SPACE(1)),SPACE(09), SPACE(1)),
SPACE(05), SPACE(1)),SPACE(03), SPACE(1)),SPACE(02), SPACE(1)),
SPACE(02), SPACE(1))
from [dbo].[SageSalesnEW] (select 'ab   d  e f g   h  i                    j' as [ADDRESS_1]) as x

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'REPLACE'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
hELLO,

DECLARE @SQL NVARCHAR(100) =  'ab   d  e f g   h  i                    j'

SELECT @SQL,REPLACE(@SQL,' ','')

Open in new window

0
 
Chris MichalczukConsultantAuthor Commented:
sorry Vikas don't understand this how do I put this into a statement that replaces Address_1 (field in Table SageSalesNew) see my previous post. I'm not a coder just a sql novice :-(

also why do you put a-j only in the statement ? remember I'm trying to get rid of any unnecessary blank or hidden space / character in a text field so only 1 real space appears between words
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Address_1,'/',' '), '  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' ') FROM SageSalesNew

Open in new window

0
 
Scott PletcherSenior DBACommented:
SELECT
 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(
[ADDRESS_1] --change to match your column name in
    --SageSalesnEW that needs spaces "singularized"
)),
 SPACE(33), SPACE(1)),SPACE(17), SPACE(1)),SPACE(09), SPACE(1)),
 SPACE(05), SPACE(1)),SPACE(03), SPACE(1)),SPACE(02), SPACE(1)),
 SPACE(02), SPACE(1))
 from [dbo].[SageSalesnEW]
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now