you are assuming that you will get two spaces in all rows, which is not true as per the data, you are getting zero in your chart index logic.
i have updated the sample data along with the your query and correct query:
CREATE TABLE booking
(
ID int IDENTITY(1,1) PRIMARY KEY,
address varchar(255)
)
insert into booking values('NORTHWICK AVENUE HARROW HA3 0AB')
insert into booking values('CHANCERY LANE LONDON WC2A 1AB')
;
CREATE TABLE Booking_ViaLocations
(
ID int IDENTITY(1,1) PRIMARY KEY,
BookingId int,
ViaLocations varchar(255)
)
insert into Booking_ViaLocations values (1,'NORTHWICK AVENUE HARROW HA3 0AB')
insert into Booking_ViaLocations values (1,'CHANCERY LANE LONDON WC2A 1AB')
insert into Booking_ViaLocations values (1,'NEW OXFORD STREET LONDON WC1A 1BA')
insert into Booking_ViaLocations values (1,'aaaaa aaa') ------ This will generate the error
---------- Query with Error
SELECT
STUFF( (
SELECT ' , Via '
+ CAST(ROW_NUMBER() OVER
(ORDER BY t1.BookingId asc) AS VARCHAR(4))
+ ' : ' +
right(t2.ViaLocations,
charindex(' ', reverse(t2.ViaLocations),
charindex(' ', reverse(t2.ViaLocations)) + 1) - 1)
FROM Booking_ViaLocations t2
WHERE t2.BookingId = t1.BookingId
FOR XML PATH ('')
)
,1,2,'') AS Names
FROM Booking_ViaLocations t1
inner join Booking b on
t1.BookingId = b.Id
GROUP BY t1.BookingId
---------- Query Without Error
SELECT
STUFF( (
SELECT ' , Via '
+ CAST(ROW_NUMBER() OVER
(ORDER BY t1.BookingId asc) AS VARCHAR(4))
+ ' : ' +
--------------- Added CASE Statement to check if CharIndex is zero
(
CASE WHEN charindex(' ', reverse(t2.ViaLocations), charindex(' ', reverse(t2.ViaLocations)) + 1) > 0 THEN
right(t2.ViaLocations,charindex(' ', reverse(t2.ViaLocations), charindex(' ', reverse(t2.ViaLocations)) + 1) - 1)
ELSE
null
END
)
--------------- Ends here
FROM Booking_ViaLocations t2
WHERE t2.BookingId = t1.BookingId
FOR XML PATH ('')
)
,1,2,'') AS Names
FROM Booking_ViaLocations t1
inner join Booking b on
t1.BookingId = b.Id
GROUP BY t1.BookingId
---------- Clearn up
drop table booking
drop table Booking_ViaLocations
hope it helps.