HOW TO REMOVE SPACING BETWEEN 2 WORDS

ben reitman

Total Post:96

Points:676
Posted by  ben reitman
 1173  View(s)
Ratings:
Rate this:

I am having one column containing both the first-name and second-name. Now i want to make a function which remove all the spaces between first name and last name and make 1st letter as as capital follow with the small letter.

For example if user type..

muKesH          AmBanI

then i will get the Output as a

Mukesh Ambani

only one space is der with 1st letter Capital by using MS SQL server.

  1. Alex Leblois

    Post:67

    Points:471
    Re: How to remove spacing between 2 words

    The best solution is to build a function. After some inspiration from the manipulations available online, I adapted it to your query;

    CREATE FUNCTION  FormatString(@text varchar(100))
    RETURNS varchar(100)
    AS
    declare @counter int,
            @length int,
            @char char(1),
            @textnew varchar(4000)
     
        ' @text = 'muKesH  AmBanI'
        set @text       = rtrim(@text)
        set @text       = lower(@text)
        set @length     = len(@text)
        set @counter    = 1
     
        set @text = upper(left(@text, 1) ) + right(@text, @length - 1)
     
        while @counter <> @length --+ 1
        begin
            select @char = substring(@text, @counter, 1)
     
            IF @char = space(1)  or @char =  '_' or @char = ','  or @char = '.' or @char = '\'
     or @char = '/' or @char = '(' or @char = ')'
            begin
                set @textnew = left(@text, @counter)  + upper(substring(@text,
    @counter+1, 1)) + right(@text, (@length - @counter) - 1)
                set @text    = @textnew
            end
     
            set @counter = @counter + 1
        end
     
        return replace(replace(replace(@text,' ','<>'),'><',''),'<>',' ')
     
    END

      Modified On Apr-08-2018 11:49:50 PM

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!