Replace on specific position - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Replace on specific position

Author

Topic

REDDY
Starting Member

43 Posts

Posted - 2003-03-26 : 16:14:57

Is there any T-sql function that will replace the specific character with given character on specified postion of a string.

Like

test1;test2; I just want to replace the last ; and final string should look like test1;test2

I went through the function REPLACE but it does replaces all the places

any help is Greately appriciated

Vish

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-26 : 16:23:06

SET @MyString = 'test1;test2;'

set @MyString = LEFT(@MyString, LEN(@Mystring)-1)

That should do it.

If you post the code that generates that string - there's a solution that will generate it without the last ;.

Sam

REDDY
Starting Member

43 Posts

Posted - 2003-03-26 : 16:32:45

Hi Sam
Thanks for your input,the code works fine if we have ; in last position but if we don't have ';' in the last postion I want to take the whole string in it's orginal form,So
What I want is to Just replace the ';' with '' if and only if finds ';' in last postion

vish

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-26 : 16:43:13

SET @MyString = 'test1;test2;'

SELECT @MyString = CASE WHEN RIGHT(@MyString),1 = ';' THEN @MyString ELSE LEFT(@MyString, LEN(@Mystring)-1) END

Brett

8-)

REDDY
Starting Member

43 Posts

Posted - 2003-03-26 : 16:56:03

Brett
Thats perfect...except a small change in Else case..

declare @MyString varchar(100)
SET @MyString = 'test1;test;' -- 'test1;test'
SELECT @MyString = CASE WHEN RIGHT(@MyString,1) = ';' THEN LEFT(@MyString, LEN(@Mystring)-1) ELSE @MyString END
select @MyString

Once again I appriciate all of you for your timely Help

With Regards
Vish

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-26 : 16:59:28

[homer]
Doooohhh

I got it backwards...good catch!!
[/homer]

Brett

8-)

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-03-26 : 17:08:14

If you don't want to use the CASE statment...


declare @String varchar(50)
SET @String = 'test1;test2' 
Select LEFT(@String, len(@String)-CHARINDEX(';',RIGHT(@String,1)))
SET @String = 'test1;test2;' 
Select LEFT(@String, len(@String)-CHARINDEX(';',RIGHT(@String,1)))

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources