Remove last character - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Remove last character

Author

Topic

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-08-27 : 08:31:47

I have a filed called single(varchar) in my table. This filed store some data like
123
45;
78011
012345;
etc

Here I want to remove the last character if it is semi-colon(;). Is there any query to do it?

Shaji

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 08:34:13

SELECT LEFT(single,CASE WHEN CHARINDEX(';',single)>0 THEN LEN(single)-1 ELSE LEN(single) END) FROM YourTable

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-27 : 08:34:41

select replace(single,';','') as single from yourtable

Madhivanan

Failing to plan is Planning to fail

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-08-27 : 08:44:25

Thanks mr.visakh16,
But if I have a record with value like
123;456;789
123;456;789;
183;856;001
then the given query removing the last letter 9,; and 1.
here if the last letter is semi-colon(;) then only need to remove that last letter.

shaji

quote:
Originally posted by visakh16

SELECT LEFT(single,CASE WHEN CHARINDEX(';',single)>0 THEN LEN(single)-1 ELSE LEN(single) END) FROM YourTable

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-27 : 08:48:51

quote:
Originally posted by shajimanjeri

Thanks mr.visakh16,
But if I have a record with value like
123;456;789
123;456;789;
183;856;001
then the given query removing the last letter 9,; and 1.
here if the last letter is semi-colon(;) then only need to remove that last letter.

shaji

quote:
Originally posted by visakh16

SELECT LEFT(single,CASE WHEN CHARINDEX(';',single)>0 THEN LEN(single)-1 ELSE LEN(single) END) FROM YourTable

This is different from your origial question
His method would work fine. Try again

Madhivanan

Failing to plan is Planning to fail

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-08-27 : 09:04:34

Its the same
The value may be 45454;878;5454;9665;5554;
here if the last letter is semi-colon then just need to remove it. Forget all other semicolon. Need to check the last character only please

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-27 : 09:10:46

declare @t table(single varchar(100))
insert into @t
select '123;456;789' union all
select '123;456;789;' union all
select '183;856;001' union all
select '45454;878;5454;9665;5554;'

SELECT LEFT(single,CASE WHEN CHARINDEX(';',single)>0 THEN LEN(single)-1 ELSE LEN(single) END) FROM @t

Madhivanan

Failing to plan is Planning to fail

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-08-27 : 09:18:13

Thanks Mr.Madhivanan for your helping

After running the above query I am getting this output
123;456;78
123;456;789
183;856;00
45454;878;5454;9665;5554
In the first one it removed '9' and in third one it removed 1 and all others are ok
Can u just post what the output u r getting?

I am using sql server 2000

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-27 : 09:28:39

declare @t table(single varchar(100))
insert into @t
select '123;456;789' union all
select '123;456;789;' union all
select '183;856;001' union all
select '45454;878;5454;9665;5554;'

select case when single like '%;' then left(single,len(single)-1) else single end as single from @t

Madhivanan

Failing to plan is Planning to fail

djam
Starting Member

1 Post

Posted - 2008-10-10 : 11:32:25

here is another alternative:
--mytable is your table :)
--mycolumn is the column you want the semicolon to disappear

--add weird string to the right of the column
update mytable
set mycolumn = mycolumn + '_weird string_'
where right (mycolumn, 1) = ';'

go

--remove semicolon just before with weird string
update mytable
set mycolumn = replace(mycolumn, ';_weird string_', '')

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-10 : 11:44:57

Another (easier?) way,


DECLARE @sample TABLE (
	[values] VARCHAR(255)
	)

INSERT @sample
select '123;456;789' union all
select '123;456;789;' union all
select '183;856;001' union all
select '45454;878;5454;9665;5554;'

SELECT
	CASE (RIGHT([values], 1))
		WHEN ';' THEN LEFT([values], LEN([values]) - 1)
		ELSE [values]
	END
FROM
	@sample

-------------
Charlie

raky
Aged Yak Warrior

767 Posts

Posted - 2008-10-11 : 00:48:29

hi try this

declare @t table(single varchar(100))
insert into @t
select '123;456;789' union all
select '123;456;789;' union all
select '183;856;001' union all
select '45454;878;5454;9665;5554;'

SELECT LEFT(single,CASE WHEN RIGHT(single,1) = ';' THEN LEN(single)-1 ELSE LEN(single) END) FROM @t

noms
Starting Member

22 Posts

Posted - 2012-09-07 : 01:57:50

can you guys help me as well, i have a similar problem where i need to remove part of the string which is in the bracket e.g 'Any abnormal noises (note below)' should be 'Any abnormal noises'

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-08 : 17:08:09

quote:
Originally posted by noms

can you guys help me as well, i have a similar problem where i need to remove part of the string which is in the bracket e.g 'Any abnormal noises (note below)' should be 'Any abnormal noises'

thanks

use logic like


SELECT 
CASE WHEN CHARINDEX('(',col)>0 
     THEN STUFF(col,CHARINDEX('(',col),CHARINDEX(')',col)-CHARINDEX('(',col) +1,'') 
     ELSE col
END 
FROM Table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

noms
Starting Member

22 Posts

Posted - 2012-09-10 : 02:31:23

thank you so much Visakh16 it worked

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-10 : 10:09:00

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources