Ignoring the second character in a field 1

[lexx2gee]

Hi all,

I am having a few problems figuring how to get a result I need for a report in query analyser. I have a table called search_nearest_park and it has two colums in it called zip and created.

Zip stores post codes

Created stores the created date for record

I need to find the top 10 most searched for post codes from the table by grouping them but the problem is some begin with two characters and some with 1. The postcodes that start with only one have a numeric value as the second character so what I need my code to do is look at the first two characters unless the second one is numeric then only look at the first character.

I have got this far

-------------------------------------------

SELECT LEFT(Zip, 2) AS zip, COUNT(Zip) AS total
FROM Search_nearest_Park
WHERE (created BETWEEN '2006/06/01 00:00:00' AND '2006/06/30 00:00:00')
GROUP BY Zip
ORDER BY total DESC

-------------------------------------------

Being a total novice I am stuck now - any help will be greatly appreciated

Paul

 

[bborissov]

DECLARE @Test Table (Zip varchar(20), Created DateTime)
INSERT INTO @Test
SELECT 'ab123123123', GETDATE() 
UNION
SELECT 'a123123123', GETDATE()-1
UNION
SELECT 'ac123123123', GETDATE()
UNION
SELECT 'ab9999999', GETDATE()-123

SELECT CASE WHEN ISNUMERIC(SUBSTRING(Zip,2,1)) = 1 THEN
                 LEFT(Zip, 1)
            ELSE LEFT(Zip, 2) END AS zip,
            COUNT(Zip) AS total
FROM @Test
WHERE (created BETWEEN '2006/08/01 00:00:00' AND
                       '2006/08/30 00:00:00')
GROUP BY CASE WHEN ISNUMERIC(SUBSTRING(Zip,2,1)) = 1 THEN
                 LEFT(Zip, 1)
            ELSE LEFT(Zip, 2) END
ORDER BY Total DESC

Is that what you want?
If so just remove decclaration of Table variable and change @Test with Search_nearest_Park in SELECT

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[lexx2gee]

That looks absolutely spot on, I have given it a try and its come back with the results I needed!

Thankyou very much for the help, you are a gentleman and a scholar

Paul

 

[bborissov]

First try it

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[lexx2gee]

I tried it and it seems to work like a charm

I needed it to look at the first two characters (such as NE for newcastle) and group them to count how many had been entered of the same type (unless the second character was a number which would mean it was a single code such as G for Glasgow)

It seems to do this so I am happy as larry - whoever he may be...

Thanks again

Paul

 

[earthandfire]

Not that this applies in your case, but there is one totally non-standard postcode

[tt]GIR 0AA[/tt]


where the first part consists totally of letters.


Hope this helps.