asked on

SQL code to ommit for Char(13)

SELECT
Address1,
Address2,
Address3,
Address4,
Address5

from
(select *,
Address1=LEFT(n,lb1-2),
Address2=SUBSTRING(n,lb1+1,lb2-lb1-2),
Address3=SUBSTRING(n,lb2+1,lb3-lb2-2),
Address4=SUBSTRING(n,lb3+1,lb4-lb3-2),
Address5=SUBSTRING(n,lb4+1,lb5-lb4-2)
from (
select *, lb5=nullif(charindex(char(10),n,lb4+1),0) from (
select *, lb4=nullif(charindex(char(10),n,lb3+1),0) from (
select *, lb3=nullif(charindex(char(10),n,lb2+1),0)from (
select *, lb2=nullif(charindex(char(10),n,lb1+1),0) from (
select *, lb1=nullif(charindex(char(10),n,0),0) from (
select *, n=DeliveryStreet+CHAR(10)
from MySalesTable
)[0])[1])[2])[3])[4])[5]) ST

I have this piece of Code that works ok if no carriage return(Char(13)) put in on the last line of the address.
If there is a carriage return then I get the following error

Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.

How can I amend the above code to not crash when a char(13) encountered ?

Jim Horn

In general you can always use T-SQL REPLACE function and char() to remove cr's

Declare @str varchar(100) = 'yabba
dabba
doo
and tigger too'

SELECT REPLACE(@str, CHAR(13), ' ')

Open in new window

ASKER CERTIFIED SOLUTION

Mlanda T

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

Some sample data with an expected result set would help.

MECR123

ASKER

hi

I have an address in a table - it is held as Varchar(250)

the addresses are shown on the screens as
Address1,
Address2,
Address3,
Address4.
Address5.

The code above will work fine if the user inputting the address does not press return after the last line of the address - if they do then my code fails with the error above. Is there a way in my Select statement above to ignore this last return carriage character if it was input by the user.

Mlanda T

Please try out the example I gave you. It does just that.

I'm just a but unsure how to integrate into your query.