0

I've got this SQL query currently:

update con set lk = '' where datalength(lk) = 1 and lk = ' '

Is this the best query to convert any spaces (or other whitespace) to an empty string in MS SQL 2008 and above?

user3513237
  • 995
  • 3
  • 9
  • 26
  • 2
    It's **best** to use a constraint and not allow that sort of thing in the first place. – SMor Aug 09 '19 at 01:23
  • [This](https://stackoverflow.com/a/35247507/92546) answer may help you with handling whitespace in general. – HABO Aug 09 '19 at 03:51

4 Answers4

0

You can try trim():

update con
    set lk = ltrim(rtrim(lk))
    where ltrim(rtrim(lk)) <> lk;

That trims all values. You can instead do:

update con
    set lk = ''
    where lk not like '%[^ ]%';  -- put all "white space" characters here
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You may use TRIM for your case. Since you want to replace empty string so LTRIM or RTRIM both can work fine

update con set lk = '' where LTRIM(lk)=''

But if you have other then space in your string then this will not work. For more advance method you need this.

update con set lk = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(1k, CHAR(10), ''), CHAR(13), ''), CHAR(9), ''))) 
where LTRIM(RTRIM(REPLACE(REPLACE(1k, CHAR(13), ''), CHAR(9), '')))=''
DarkRob
  • 3,843
  • 1
  • 10
  • 27
0
select * from con where lk like '% %'
Dr.Stark
  • 116
  • 1
  • 4
0

Keeping in mind that the LEN of any string made up of only spaces will be 0, you can do this (no trimming or replacing required):

UPDATE con SET lk = ''
WHERE LEN(lk) + PATINDEX('%[^'+CHAR(9)+CHAR(10)+CHAR(13)+']%',lk) = 0;

I included CHAR(9),10 & 11 in my code which is most common. You can get a complete list of Unicode whitespace characters here. If you are are dealing with VARCHAR you only need to worry about CHAR's less than 255: 9,10,11,12,13,32,133,160.

Alan Burstein
  • 7,770
  • 1
  • 15
  • 18