Tuesday, 25 June 2013

How to replace multiple spaces with one space?

I came up with the situation of replacing multiple spaces with single space in SQL. I got the solution from a site but it was very difficult to understand at first look. So I got into deep to find out exactly how it works?

I really liked the trick and it made me write this blog post. This could be useful where regex expressions are not available.

The logic is explained in the video below. For visualization purpose space is represented as ‘@’ character and ‘#’ can be any random character which never occurs in strings as a normal character.

I am repeating again,
Consider
@ = white space
# = any random character that is never used as a normal character in strings.

[sql]
replace(
replace(
replace( trim(‘string’) , ‘@@’,’@#’ )
,’#@’,’’)
,’#’,’’)[/sql]



By the way the editor utility which I am using in this video is Notepad++ which is free and open source editor and a video utility to create video which I used is CamStudio it is also free and open source.

No comments:

Post a Comment