How to remove carriage returns from a field. 1

[Corr]

Hello,

Does anyone know how to remove the first carriage return in a field? Also removing the data up to the first carriage return may be nice as well.

Table Name = ADDRESS
Column Name = STREET_ADDRESS

The stree_address field contains a company name then a Carriage Return followed by a street address. I would like to remove all data up to and including the first carriage return so that only the street address is remaining in the field.


Any and all help is appreciated.

Thanks,

John

 

[mwolf00]

SELECT substring(myField, inStr(char(13)), len(myField)-inStr(char(13))) FROM myTable Get the Best Answers! faq333-2924
"A witty saying proves nothing." - Voltaire
mikewolf@tst-us.com

 

[AWithers]

try something like this

SELECT substring(yourfield,charindex(char(13),yourfield)+1,100)from yourtable


Charindex is the position of the first carriage return (Char(13)) 100 is a nominal number the length of the string after the carriage return

see the replace function if you want to replace


HTH

Andy

 

[sguslan]

try :

update address
set
street_address= substring(street_address,charindex(char(13),street_address),len(street_address)-charindex(char(13),street_address))

But first test it on a temporary table, as i have no means to test it out...

 

[mwolf00]

OOPPSS! A little VB came through in my post... Sorry, charIndex should be used where I wrote inStr:

SELECT substring(myField, charIndex(char(13)), len(myField)-charIndex(char(13))) FROM myTable Get the Best Answers! faq333-2924
"A witty saying proves nothing." - Voltaire
mikewolf@tst-us.com

 

[Corr]

MWOLF00,

I get an error that CHARINDEX requires 2 or 3 arguments. I'm not too familiar with the charindex or I would try to modify it. If you send me another query I will try it as well if you'd like. Thanks for your help.

AWITHERS,

The query worked just fine. Thank You!

SGUSLAN,

I will try your query soon.

 

[mwolf00]

SELECT substring(myField, charIndex(char(13), myField), len(myField)-charIndex(char(13),myField)) FROM myTable

Trying to wake up Get the Best Answers! faq333-2924
"A witty saying proves nothing." - Voltaire
mikewolf@tst-us.com