how to get data PostCode last two after two space sql server
-
i have following query i want only highlighted post code how to get this please help me i want result like this Via1: PostCode, Via2 :PostCode ,Via3: PostCode
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')
SELECT
STUFF( (
SELECT ' , Via '
+ CAST(ROW_NUMBER() OVER
(ORDER BY t1.BookingId asc) AS VARCHAR(4))
+ ' : ' +
right(t2.ViaLocValue,
charindex(' ', reverse(t2.ViaLocValue),
charindex(' ', reverse(t2.ViaLocValue)) + 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
-
I'm not sure what you're asking here. Your query looks like it already returns:
Via 1 : HA3 0AB , Via 2 : WC2A 1AB , Via 3 : WC1A 1BA
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
-
but my sql server 2008 its show error
Invalid length parameter passed to the RIGHT function.
-
Not with the data you've posted, it doesn't. I'm guessing the reason you get the error is that you're running the query against one or more rows that don't fit the pattern of having at the end a two-part postcode with a space in the middle. You'll need to clean your data and/or tweak your query so that it handles such cases.
John
-
how to solve this problem
-
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.
-
I'm asking a stupid question by asking why doesn't the post code have its own column?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply