CODEWITHSUNDEEP
sql
user979331
user979331

Reputation: 11851

SQL get rid of extra spaces

I have some data here that goes like this:

'US     ' -- (5 spaces)

and I have over 300 records like this, is there a way to get rid of those 5 spaces?

Upvotes: 1

Views: 10312

Answers (3)

Kermit
Kermit

Reputation: 34055

Use RTRIM to remove any trailing blanks after a string.

SELECT RTRIM(col1) FROM tbl1

Additionally, you can use LTRIM to remove any leading blanks.

To my knowledge, this function works in SQL Server, Oracle, MySQL and PL/SQL.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269533

Most databases support an RTRIM() or TRIM() function.

You can also replace the spaces with empty strings:

select replace('abcde   ', ' ', '')

(The replace function may be called something different in different databases.)

Another possibility is that the underlying type is CHAR rather than VARCHAR. In your example, the column might be declared as char(7) rather than varchar(7). If that is the case, then you cannot remove the spaces, because the value is padded out to seven characters.

The best thing to do in this case is to change the definition to varchar(7).

Upvotes: 0

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32680

You can use RTRIM and LTRIM to remove trailing or leading spaces.

If you're just selecting the record:

SELECT RTRIM(State) AS State
FROM YourTable

If you want to update the table to remove the spaces:

UPDATE YourTable 
SET State = RTRIM(State)

Upvotes: 4

Related Questions