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.

Comments

Post a Comment

Popular posts from this blog

How to get local time from UTC using Moment.JS

Image
  Moment.JS is a beautiful javascript library for parsing, validating, manipulating, and formatting dates. This is a must have utility library when it comes to manipulating timestamps and dates in javascript. Here I will show how to get local time if you have a utc date time string. Lets say you have a UTC date-time string as  2014-02-19 05:24:32 AM and you want to determine time in your timezone then use following code: moment.utc('2014-02-19 05:24:32 AM').toDate(); toDate() method gives javascript Date() object. Below I have shared an example of how to use it. You can click on JavaScript link below to see the code.
Read more

Partially rendering Handlerbars from Backbone view

Image
While working on a project I had this situation where I wanted to render a view partially after some ajax is done. In this post I will explain how to render HandlerBar views partially. For the purpose of demonstrate I will simulate delay using setTimeout() function instead of an ajax request. So for an example lets consider following setup 1. Template <script id="PersonTemplate" type="text/x-handlebars-template"> <div> {{firstName}} {{lastName}} <div> <div id="divPartial"> <a href="{{website}}">{{website}}</a> </div> </script> 2. Backbone Model var PersonModel = Backbone.Model.extend({ defaults: { firstName: 'Bharat', lastName: 'Patil', website: '' } }); 3. Backbone view using Handlerbars view var TestView = Backbone.View.extend({ //assign model view instance model: objPerson, //get the
Read more

6 steps to create local copy of your live WordPress site

I know there are bunch of other posts on how to create local copy of your live WordPress site, however, they are too much descriptive. Here I will show you step by step approach rather than descriptive one. 1. Export your live database into an sql file. 2. Download/Copy your live WordPress site folder to your local machine. 3. Move your downloaded WordPress site files inside local server’s web root. 4. Open exported sql file using notepad or any other editor of your choice and replace “http://www.yoursite.com” with “http://localhost/wordpress” and Save that file. (CAUTION: This file could be very large so replacing might take time. Command for replacing file content on *nix based system.) 5. Import database from that sql file into your local mysql database. 6. Open wp-config.php file which you can find inside your copied WordPress folder. In that file replace DB_NAME with your local WordPress database which you imported in above step. DB_USER with database user name. D
Read more