Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (2.6k points)

I'm in the process of trying to clear out leading and trailing spaces from an NVARCHAR(MAX) column that is filled with prices (using NVARCHAR due to data importing from multiple operating systems with odd characters).

At this point, I have an at-SQL command that can remove the leading/trailing spaces from static prices. However, when it comes to leveraging this same command to remove all prices, I'm stumped.

Here's the static script I used to remove a specific price:

UPDATE *tablename* set *columnname* = LTRIM(RTRIM(2.50)) WHERE cost = '2.50 ';

1 Answer

0 votes
by (7.2k points)
edited by

To use LTRIM/RTRIM to search and replace leading/trailing spaces in SQL you need to follow this step...

To remove spaces from left we use LTRIM

To remove spaces from right we use RTRIM

UPDATE *tablename* 

SET *columnname* = LTRIM(RTRIM(*columnname*));

would have worked on ALL the rows. To minimize updates if you don't need to update, the update code is unchanged, but the LIKE expression in the WHERE clause would have been

UPDATE [tablename] SET [columnname] = LTRIM(RTRIM([columnname])) WHERE 32 in (ASCII([columname]), ASCII(REVERSE([columname])));

Intellipaat provides Online SQL Training for its learners. 

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
asked May 1, 2021 in Java by sheela_singh (9.5k points)
0 votes
1 answer
asked Oct 14, 2019 in Python by Sammy (47.6k points)

31k questions

32.8k answers

501 comments

693 users

Browse Categories

...