Remove Hyphen from End of String

Question

Post reply

Remove Hyphen from End of String

GF

Hall of Fame

Points: 3774
More actions
July 13, 2014 at 10:19 pm

#99027

I have a table called Cars and this table has several columns but I am interested in only one column named Description
Several hundred rows have a hyphen '-' at the end of the description.
I am trying to update the table and set the description column so that the hyphen at the end is removed.
The string can have hyphens in it just not at the end.
The column is an nvarchar(255)
here is what I have tried.
update Cars
set [description] = replace(right([description],1),'-','')
where right([description],1) like '%-'
My results are a blank description field for that row.
I have a sense this should be obvious but I can't seem to figure it out.
Thanks in Advance.
Gary

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

This should get you passed the hurdle

😎

USE tempdb;

GO

DECLARE @Cars TABLE

(

Car_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL

,Description NVARCHAR(255) NULL

);

INSERT INTO @Cars(Description)

VALUES

(N'General Car Description 1')

,(N'General Car Description 2~')

,(N'General Car Description 3')

,(N'General Car Description 4~')

,(N'General Car Description 5')

,(N'General Car Description 6~');

;WITH CLEANED_CARS AS

(

SELECT

C.Car_id

,CASE

WHEN RIGHT(C.Description,1) = N'~' THEN STUFF(C.Description,LEN(C.Description),1,N'')

ELSE C.Description

END AS Description

FROM @Cars C

)

UPDATE C

SET C.Description = CC.Description

FROM @Cars C

INNER JOIN CLEANED_CARS CC

ON C.Car_id = CC.Car_id

WHERE C.Description LIKE N'%~';

SELECT

*

FROM @Cars

Results

Car_id Description

----------- ---------------------------

1 General Car Description 1

2 General Car Description 2

3 General Car Description 3

4 General Car Description 4

5 General Car Description 5

6 General Car Description 6

GF Hall of Fame Points: 3774 More actions · Answer 2

Thank you for your reply.

I am using SQL 2000, sorry I did not mention that.

Thanks

Gary

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 3

GF (7/13/2014)
Thank you for your reply.
I am using SQL 2000, sorry I did not mention that.
Thanks
Gary

Sorry about that, my bad and a typical BFC (Before First Coffee) syndrom:-P

😎

Here is a SQL2000 version

USE tempdb;

GO

CREATE TABLE dbo.Cars

(

Car_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL

,Description NVARCHAR(255) NULL

);

INSERT INTO dbo.Cars(Description)

VALUES

(N'General Car Description 1')

,(N'General Car Description 2~')

,(N'General Car Description 3')

,(N'General Car Description 4~')

,(N'General Car Description 5')

,(N'General Car Description 6~');

UPDATE C

SET C.Description = CC.Description

FROM dbo.Cars C

INNER JOIN

(

SELECT

C.Car_id

,CASE

WHEN C.Description LIKE N'%~' THEN SUBSTRING(C.Description,1,LEN(C.Description)-1)

ELSE C.Description

END AS Description

FROM dbo.Cars C

) AS CC

ON C.Car_id = CC.Car_id

WHERE C.Description LIKE N'%~';

SELECT

*

FROM dbo.Cars

DROP TABLE dbo.Cars;

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Using the KISS method, this should work ok in SQL Server 2000. Details are in the comments,

--===== Build a test table.

-- This is not a part of the solution.

DECLARE @Cars TABLE

(

Car_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL

,Description NVARCHAR(255) NULL

)

;

--===== Populate the test table.

-- This is not a part of the solution

INSERT INTO @Cars

(Description)

SELECT N'General Car Description 1' UNION ALL

SELECT N'General Car Description 2-' UNION ALL

SELECT N'General Car Description 3' UNION ALL

SELECT N'General Car Description 4-' UNION ALL

SELECT N'General Car Description 5' UNION ALL

SELECT N'General Car Description 6-' UNION ALL

SELECT N'General Car Description 7 -'

;

--===== Remove the trailing hyphen from rows that have it.

UPDATE @Cars

SET Description = RTRIM(SUBSTRING(Description,1,LEN(Description)-1))

WHERE Description LIKE '%-'

;

--===== Let's see what we have as a result.

SELECT * FROM @Cars

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 5

Ouch....of course my code is overly complicaded and will fail on 2K because of the values clause. More coffee...

😎

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

Heh... you're not alone... lack of coffee has caused more than one mistake on my part. That's why I have to keep it simple... "so I doan gotta think 'aboud it". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GF Hall of Fame Points: 3774 More actions · Answer 7

First let me say think you to both of you for your responses.

The TRIM(SUBSTRING([description],1,LEN([description])-1))

Worked as I hoped it would.

However, I would like to know why the Replace(right(description,1),'-','') did not work.

I have used the Replace function many times with success and the right function as well, I just never combined them before.

So if someone could explain I would be very grateful.

Thanks

Gary

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 8

My bad or rather a lack of tanker sized industrial strength coffee this morning, the RIGHT function isn't available in SQL Server 2000, sorry about the confusion here.

😎

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

GF (7/14/2014)
First let me say think you to both of you for your responses.
The TRIM(SUBSTRING([description],1,LEN([description])-1))
Worked as I hoped it would.
However, I would like to know why the Replace(right(description,1),'-','') did not work.
I have used the Replace function many times with success and the right function as well, I just never combined them before.
So if someone could explain I would be very grateful.
Thanks
Gary

To be sure, there's no TRIM function... it's RTRIM.

The reason why the Replace(right(description,1),'-','') thing didn't work is because once you say RIGHT(somecol,1), that makes a separate string no longer associated with the original string. You successfully replaced the dashes in that separate string but you didn't apply that to the original string.

You could have used the STUFF function with the help of the LEN function, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

Eirikur Eiriksson (7/14/2014)
My bad or rather a lack of tanker sized industrial strength coffee this morning, the RIGHT function isn't available in SQL Server 2000, sorry about the confusion here.
😎

We're going to have to hook you up IV. 😛

http://technet.microsoft.com/en-us/library/aa258891(v=sql.80).aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)