CODEWITHSUNDEEP
sqlsql-server
Rupesh S
Rupesh S

Reputation: 125

How to replace '&' character at the last comma in a string?

My input string in 

Declare @ListOfContactType nvarchar(1000) 
SET @ListOfContactType = 'Customer,Supplier,Financier,Employee'

Please help me to get output as 'Customer,Supplier,Financier & Employee'

Note: string in @ListOfContactType may vary.

Upvotes: 0

Views: 2387

Answers (5)

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

Find the last occurence of , with the help of charindex and replaced that with & by using stuff function. 

DECLARE @LISTOFCONTACTTYPE NVARCHAR(1000)

SET @LISTOFCONTACTTYPE = 'CUSTOMER,SUPPLIER,FINANCIER,EMPLOYEE'

SELECT STUFF(@LISTOFCONTACTTYPE, 
             LEN(@LISTOFCONTACTTYPE) - CHARINDEX(',', REVERSE(@LISTOFCONTACTTYPE))+1, 1, ' & ') AS OUT_PUT

Upvotes: 1

Pankaj Kumar
Pankaj Kumar

Reputation: 21

Solution:

Declare @ListOfContactType nvarchar(1000);
Declare @Position int;

Set @ListOfContactType = 'Customer,Supplier,Financier,Employee';
Set @Position = CHARINDEX(',', REVERSE(@ListOfContactType));

select REVERSE(STUFF(REVERSE(@ListOfContactType), @position, 1, '&'))

Expected Output:

Customer,Supplier,Financier&Employee

Reverse the string to get the index, than reverse again after replacing for expected output.

Upvotes: 2

Paresh J
Paresh J

Reputation: 2419

Simple and very straightforward query:

Declare @ListOfContactType nvarchar(1000) 
SET @ListOfContactType = 'Customer,Supplier,Financier,Employee'

select reverse(stuff(reverse(@ListOfContactType),charindex(',',reverse(@ListOfContactType)),1,' & '))

Upvotes: 0

Bill Tür stands with Ukraine
Bill Tür stands with Ukraine

Reputation: 3515

You have to find the last occurance of a comma in your string. Since T-SQL doesn't offer a way to search backwards the trick is to reverse the string and search from the beginning.

LEN(@ListOfContactType) - CHARINDEX(',', REVERSE(@ListOfContactType))

Will give you this position. You can then replace the comma with STUFF but keep in mind that you have to correct the position there for one character. So

STUFF( @ListOfContactType, 1 + LEN(@ListOfContactType) - CHARINDEX(',', REVERSE(@ListOfContactType)), 1, ' & ')

should give your desired result.

Upvotes: 0

Poonam
Poonam

Reputation: 679

Declare @ListOfContactType nvarchar(1000) 
Declare @LastOccuredAt int
SET @ListOfContactType = 'Customer,Supplier,Financier,Employee'
SET @LastOccuredAt=LEN(@ListOfContactType) - CHARINDEX(',',REVERSE(@ListOfContactType)) 
print substring(@ListOfContactType,1,@LastOccuredAt) + ' & ' + substring(@ListOfContactType,@LastOccuredAt +2,(len(@ListOfContactType)-@LastOccuredAt))

Upvotes: 0

Related Questions