Products

Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller

Developer

Article Blog Forum Interview Beginner News Startup Quiz

Users

Career

Apply Job Internship

Pricing

Login Sign Up Get a Quote

How to remove spacing between 2 words

Anonymous User 2335 21 Jan 2015

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

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

1 Answers

Anonymous User 21 Jan 2015

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

MindStick Training

Markdown for AI

A clean, structured version of this page for AI assistants and LLMs.