Home > DeveloperSection > Forums > How to remove spacing between 2 words
ben reitman
ben reitman

Total Post:96

Points:676
Posted on    January-21-2015 10:29 PM

 MSSQL Server SQL Server  SQL 
Ratings:


 1 Reply(s)
 667  View(s)
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.



Alex Leblois
Alex Leblois

Total Post:67

Points:471
Posted on    January-21-2015 10:59 PM

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


Don't want to miss updates? Please click the below button!

Follow MindStick