SQLTeam.com | Weblogs | Forums

Find and Replace last <N_L> Characters with Hyphen


#1

Hi All

I want to find if the last characters of a string is <N_L> (Next Line Character) and then it needs to be replaced with Hyphen

Sample
740448G0002 740098G0002 <N_L> with 740448G0002 740098G0002 ADD ''
Alphaacure<N_L> Alphaacure (Hyphen)

Shreek


#2

I'm not understanding what you are asking. It's just a guess but try looking up the Stuff function in SQL Server Books Online


#3

Maybe you can use this

declare @lf varchar(2)=char(13)+char(10); /* Windows linefeed */
--declare @lf varchar(1)=char(10);          /* *nix linefeed */

select left(yourfield,len(yourfield)-case when yourfield like '%'+@lf then len(@lf) else 0 end)
  from yourtable
;

Edit: forgot the hyphen, so here's another solution

declare @lf varchar(2)=char(13)+char(10); /* Windows linefeed */
--declare @lf varchar(1)=char(10);          /* *nix linefeed */

select case when yourfield like '%'+@lf then left(yourfield,len(yourfield)-len(@lf))+'-' else yourfield end
  from yourtable
;