I have used REPLACE () method to remove the
special characters
DECLARE @A
VARCHAR(50), @B VARCHAR(50)
SELECT @A =
‘101;#Admin-Ram Chandran’, @B = ‘140;#Ram chandran’
SELECT CAST( REPLACE(
REPLACE( REPLACE( SUBSTRING( @A, CHARINDEX (‘;’, @A), LEN(@A)),
‘;’,’’),’#’,’’), ‘;’, ‘’) AS NVARCHAR(50)) AVALUE
, CAST( REPLACE(
REPLACE( REPLACE( SUBSTRING( @B, CHARINDEX (‘;’, @B), LEN(@B)),
‘;’,’’),’#’,’’), ‘;’, ‘’) AS NVARCHAR(50)) BVALUE
