Deleting First occurance of Char(13) 2

[GKWilly]

moving on from my problem earlier I now have a slightlty similar one.

I have table and data similar to this;

ID Address
1 1 first ave
widnes
Cheshire
2 -----extra unwanted carriage return
2 First Ave
widnes
Cheshire

The addess is allowed carriage returns but I want to delete the blank line (extra carriage return) at the start of the address.
My syntax is:-

Update MyTable
set address = REPLACE ( address , (charindex (CHAR(13)=1),t2.a_name),'')
FROM MyTable

where charindex (CHAR(13),address) = 1

I'm sure you can see the problem with the =1 in the set statement. how do I delete the line but keep the others?
Also do I need to replace CHAR(13)+CHAR(10) or would that screw up the 2nd line in the address which contain legitimate data?

Cheers

 

[jhall01]

try:

UPDATE MyTable
  set address = CASE 
  WHEN charindex(char(13),a_name) = 1 
    THEN stuff(a_name,charindex(char(13),a_name),2,'')
    ELSE a_name
  END

 

[EdwinGene]

While 1 = 1
Begin

Update MyTable
Set Address = SubString(Address, 2, 999)
Where CharIndex(Char(13), Address) = 1

If @@RowCount = 0
Break

End


The loop will ensure that any multiple leading Char(13)'s are removed.

 

[jhall01]

My previous code would update all records use the following to only update the necessary records:

UPDATE MyTable
  set address = stuff(a_name,1,2,'')
Where charindex(char(13),a_name) = 1

I also made the mistake of extra code within the stuff function, since we know the char(13) will always be in the first position we can place "1" in the stuff function instead of the charindex code