Non-breaking Space in String Value

Developer tries to truncate the space in string but there is a problem found and told me about the space is not remove from string even a using LTRIM & RTRIM functions.

First is how to find which character used in string instead of space. Here I have lookup the SQL query result below.

SELECT Candidateid ,’|’ +  LTRIM(RTRIM(UPPER(FirstName))) + ‘|’  FirstName,

LTRIM(RTRIM(UPPER(LastName)))  LastName

FROM CandidateMaster Where CandidateID = 991847

 

Query Result

Candidateid	FirstName	LastName
991847	|KESHELL |	DURU

 

Here, ‘keshell’ with some character is joined, but which character is it I want to find.

‘KESHELL ’ Copy only this character and find with ASCII function.

Select ASCII(‘ ‘)

Result is 160, that means it is ASCII Char no 160 (Non-breaking space) and Normal space ASCII char no. is 32. I have found the non-breaking space in this string, but how can I remove it from string. I can use REPLACE function for removing a non-breaking space.

SELECT Candidateid , ‘|’ + REPLACE(FirstName,char(160), ”) + ‘|’ AS FirstName,

LTRIM(RTRIM(UPPER(LastName))) LastName

FROM CandidateMaster Where CandidateID = 991847

Query Result

Candidateid	FirstName	LastName
991847	|Keshell|	DURU

CHAR(160) (or   widely used in HTML) that can appear in VARCHAR and NVARCHAR is not removed by RTRIM & LTRIM:

 

SQL Functions can truncate the all marking blanks from the string. Here are sql functions

SUBSTRING(string, start_pos, length)	Returns a substring
REPLACE(string, what, with)	Replaces a substring or character in string