Remove spacing between 2 words

Question

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.

Why do you store two informations in one column at all? Solve the real problem instead of fighting against the symptoms. — Tim Schmelter, Aug 08 '14 at 07:40
@TimSchmelter no i m storing in two column only but many users type there 1st name as well as middle name in the same Textbox and by mistake dey place mny spaces also so i wanna remove them. — Vishnu, Aug 08 '14 at 07:43
_"then i will get the Output as a"_ actually means that this is what you want to get? — Tim Schmelter, Aug 08 '14 at 07:44

TechDo · Answer 1 · 2014-08-08T10:27:22.420

Please try:

declare @var nvarchar(500)='muKesH    AmBanI'

select 
    STUFF(FN, 1, 1, UPPER(LEFT(FN, 1)))+' '+
    STUFF(LN, 1, 1, UPPER(LEFT(LN, 1)))
FROM(
    select 
        LOWER(SUBSTRING(@var, 1, charindex(' ', @var)-1)) FN,
        LOWER(LTRIM(SUBSTRING(@var, charindex(' ', @var), 500))) LN
)x

AS function

CREATE FUNCTION  FormatString(@text varchar(100))
RETURNS varchar(100)
AS 
BEGIN

    SET @text=@text+' '

    SELECT @text=
        STUFF(FN, 1, 1, UPPER(LEFT(FN, 1)))+' '+
        ISNULL(STUFF(LN, 1, 1, UPPER(LEFT(LN, 1))), '')
    FROM(
        SELECT 
            LOWER(SUBSTRING(@text, 1, charindex(' ', @text)-1)) FN,
            LOWER(RTRIM(LTRIM(SUBSTRING(@text, charindex(' ', @text), 500)))) LN
    )x

RETURN @text
END

score 2 · Accepted Answer · answered Aug 08 '14 at 08:02

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

thank you so much i got it now this is the Pure answer which i want to get. a perfect work for my project. Thanks alot — Vishnu, Aug 08 '14 at 08:26

mohan111 · Answer 3 · 2014-08-08T08:51:53.423

1

modified query according to output

DECLARE @t varchar(50)
SELECT @t = 'mukesh          AmBanI'
SELECT UPPER(LEFT(@t,1))+ SUBSTRING(REPLACE(REPLACE(RTRIM(LTRIM(@t)),'  ',' '),'  ',''),2,5)+' '+
LEFT(LTRIM(REVERSE(LEFT(REVERSE(@t),PATINDEX('% %',@t )))),1)+LOWER(SUBSTRING(LTRIM(REVERSE(LEFT(REVERSE(@t),PATINDEX('% %',@t )))),2,5))

edited Aug 08 '14 at 08:51

answered Aug 08 '14 at 08:09

mohan111

8,633
4
28
55

This doesn't init cap. – Patrick Hofman Aug 08 '14 at 08:12
@PatrickHofman you want make the result as the same out put above shown.making capital letters also... – mohan111 Aug 08 '14 at 08:16
That's the request from OP, yes. – Patrick Hofman Aug 08 '14 at 08:17

score 0 · Answer 4 · answered Aug 08 '14 at 08:57

Try this:

DECLARE @Name VARCHAR(100) = 'muKesH          AmBanI'
DECLARE @Output VARCHAR(100)
SET @Output = LOWER(STUFF(@Name, CHARINDEX(' ',@Name), LEN(@Name)-CHARINDEX(' ',REVERSE(@Name))+1-CHARINDEX(' ',@Name)+1,' '))
SET @Output = STUFF(@Output, CHARINDEX(' ',@Output)+1, 1, UPPER(SUBSTRING(@Output,CHARINDEX(' ',@Output)+1,1)))
SET @Output = UPPER(LEFT(@Output, 1)) + RIGHT(@Output, LEN(@Output)-1)

SELECT @Output

Remove spacing between 2 words

4 Answers4