How can I use LTRIM RTRIM to search and replace leading trailing spaces

0 votes

I'm attempting to remove leading and trailing spaces from an NVARCHAR(MAX) column that contains price information (using NVARCHAR due to data importing from multiple operating systems with odd characters).

The leading and following spaces from static prices can now be removed using a t-SQL statement. I'm at a loss for how to use this identical command to eliminate all pricing, though.

The static script I used to eliminate particular pricing is shown here:

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

Here's what I've tried to remove all the trailing spaces:

UPDATE *tablename* set *columnname* LIKE LTRIM(RTRIM('[.]')) WHERE cost LIKE '[.] ';

I've also experimented with various the% for random character variations, but I'm currently at a loss for ideas.

I want to remove all the leading and following spaces from each cell in this column with just one straightforward command, without changing any of the column data itself.

Sep 8, 2022 in Database by Kithuzzz
• 38,000 points
1,152 views

1 answer to this question.

0 votes

Use LTRIM/RTRIM to eliminate spaces from the left and right. What you had:

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

Would have worked on ALL the rows. The update code remains the same to reduce updates if you don't need to update, 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])));

I hope this helps you.

answered Sep 10, 2022 by narikkadan
• 63,640 points

Related Questions In Database

0 votes
1 answer

I have to build a school website How can i connect databases to the instance (ec2) and what is the best AMI for my webserver INAWS ???

Hi@Trinu, I don't think your database is depends ...READ MORE

answered May 4, 2020 in Database by MD
• 95,460 points
925 views
0 votes
1 answer

How can I connect databases server to EC2 server?

Hi@Trinu, You can create one database in AWS ...READ MORE

answered May 1, 2020 in Database by MD
• 95,460 points
1,033 views
0 votes
1 answer

How Can I use "Date" Datatype in sql server?

There's problem in all of them and ...READ MORE

answered Feb 9, 2022 in Database by Neha
• 9,020 points
1,332 views
0 votes
0 answers

How would I use excel to generate a large update sql statement?

I know there's a way to have ...READ MORE

Mar 30, 2022 in Database by Edureka
• 13,690 points
2,340 views
0 votes
1 answer
0 votes
1 answer

How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

answered Feb 3, 2022 in Database by Vaani
• 7,070 points
806 views
0 votes
0 answers

How do I UPDATE from a SELECT in SQL Server?

INSERT INTO Table (col1, col2, col3) SELECT col1, ...READ MORE

Feb 4, 2022 in Database by Vaani
• 7,070 points
532 views
0 votes
1 answer

What is a stored procedure?

A stored procedure is a set of ...READ MORE

answered Feb 4, 2022 in Database by Neha
• 9,020 points
1,045 views
0 votes
1 answer

How can I confirm a database is Oracle & what version it is using SQL?

Run this SQL: select * from v$version; Output: BANNER ---------------------------------------------------------------- Oracle Database ...READ MORE

answered Sep 12, 2022 in Database by narikkadan
• 63,640 points
680 views
0 votes
1 answer

How can I import an Excel file into SQL Server?

You can use OPENROWSET to import an Excel file ...READ MORE

answered Sep 15, 2022 in Database by narikkadan
• 63,640 points
2,000 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP