• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 626
  • Last Modified:

SQL Syntax - Remove blank space Padding from string

I have data stored in a string column, and it has white space blank padding on either side:

table: person

name
'     john   '
' jane  '

I would like to remove the left and right space padding on either side - and the padding length is unknown.
Something like:?

Left(name, CHARINDEX (' ', name, CHARINDEX (' ', name) + 1) - 1)

Open in new window

0
robthomas09
Asked:
robthomas09
3 Solutions
 
rushShahCommented:
try this..

SELECT ltrim(rtrim(name))
0
 
k_murli_krishnaCommented:
TRIM(name) will do the job for you.
0
 
k_murli_krishnaCommented:
Sorry TRIM is not there in SQL Server. rushShah is correct. You can also use:
SELECT rtrim(ltrim(name))
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
k_murli_krishnaCommented:
This removes all whitespace, including tabs, carriage returns and line feeds. Further, it can be easily modified to trim any set of characters – just make @charstotrim a parameter.

CREATE FUNCTION dbo.trim(@input varchar(8000)) returns varchar(8000) as
begin
declare @charstotrim varchar(100)
select @charstotrim =’ ‘+char(9)+char(10)+char(13)
while charindex(left(reverse(@input),1),@charstotrim) >0
select @input=left(@input,datalength(@input)-1)
while charindex(left(@input,1),@charstotrim) >0
select @input=substring(@input,2,datalength(@input)-1)
return @input
end
0
 
Rajkumar GsSoftware EngineerCommented:
To remove right & left spaces in all data, execute this query

UPDATE urTable SET urColumn = LTRIM(RTRIM(urColumn ))

Replace with your actual table name & column name to update

Now you can verify using this query

SELECT urColumn FROM urTable

Raj
0
 
robthomas09Author Commented:
thanks - will try and let you know
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now