---
title: "How to remove spacing between 2 words"  
description: "How to remove spacing between 2 words"  
author: "Anonymous User"  
published: 2015-01-21  
updated: 2015-01-21  
canonical: https://www.mindstick.com/forum/12888/how-to-remove-spacing-between-2-words  
category: "mssql server"  
tags: ["sql server", "sql"]  
reading_time: 2 minutes  

---

# How to remove spacing between 2 words

I am having one [column](https://www.mindstick.com/forum/33860/how-to-calculate-column-summary-in-sql-server) containing both the first-[name](https://yourviews.mindstick.com/view/87450/how-to-generate-a-brand-name-using-linkedin-comprehensive-guide) and [second](https://answers.mindstick.com/qa/41761/how-did-the-second-industrial-revolution-influence-women-s-roles-in-society)-name. Now i want to make a [function](https://www.mindstick.com/articles/13001/multi-statement-table-valued-user-defined-function-in-sql-server) which [remove](https://yourviews.mindstick.com/story/4554/8-harmful-weeds-to-remove-from-garden) 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](https://www.mindstick.com/interview/34427/explain-the-output-in-angular) as a

[Mukesh Ambani](https://answers.mindstick.com/qa/49499/what-is-mukesh-ambani-net-worth)

only one [space](https://www.mindstick.com/articles/12954/do-your-electrical-repair-in-your-own-space) is der with 1st letter Capital by using MS [SQL server](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server).

## Replies

### Reply by Anonymous User

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
```


---

Original Source: https://www.mindstick.com/forum/12888/how-to-remove-spacing-between-2-words

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.