SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Wildcards
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 4

IainT
Starting Member

12 Posts

Posted - 01/12/2010 :  07:49:47  Show Profile  Reply with Quote
Hey guys,

I have what I hope is a pretty simple question. It feels like the sort of thing I should be able to find pretty easily on Google, but I'm struggling.

I have an addresses table which contains the entry:

BA9

I want to select it, and anything else which goes LetterLetterNumber.

I thought this would work:

SELECT
*
FROM
Addresses
WHERE
PostCode LIKE '[A-Z][A-Z][0-9]'

but that comes up with no results.

This does work:

SELECT
*
FROM
Addresses
WHERE
PostCode LIKE '%[A-Z][A-Z][0-9]'

but it would also select, for example:

ThisIsARubbishEntry0

Does anyone have any idea why the first one doesn't match and how I can restrict a selection to just three charactes in the format LetterLetterNumber?

Apologies if I'm being really stupid.

Thanks
Iain

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 01/12/2010 :  07:55:54  Show Profile  Visit webfred's Homepage  Reply with Quote
Your query should work but only if PostCode is exactly 'BA9'.
Can you show the whole value of the field?


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 01/12/2010 :  07:56:04  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
It should be

SELECT
*
FROM
Addresses
WHERE
PostCode LIKE '[A-Z][A-Z][0-9]%'

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/12/2010 :  08:31:45  Show Profile  Reply with Quote
Is PostCode a CHAR datatype?, or does it have trailing [or leading] spaces?

SELECT TOP 100 *
FROM Addresses
WHERE RTrim(LTrim(PostCode)) LIKE '[A-Z][A-Z][0-9]'

The functions on the left side of the WHERE will kill performance, so not recommended for regular use on large tables.
Go to Top of Page

IainT
Starting Member

12 Posts

Posted - 01/12/2010 :  09:53:14  Show Profile  Reply with Quote
Hey,


Webfred - the value is exactly BA9. No more no less.


madhivanan - the function you give here would also return

"BA9 The rest of this field is rubbish"

but I only want the exact value "BA9"


Kristen - it is a varchar(15).


Thanks for your help guys, but I don't think we're there yet.

As another example, this returns no rows:

SELECT
*
FROM
Addresses
WHERE
adrZipCode LIKE '[A-Z]%' AND
LEFT(adrZipCode, 3) = 'BA9'


This returns 18 rows though, all starting with or equal to BA9:

SELECT
*
FROM
Addresses
WHERE
adrZipCode LIKE '%' AND
LEFT(adrZipCode, 3) = 'BA9'



It seems to have a problem with square brackets at the start of the like.

Thanks
Iain
Go to Top of Page

DP978
Constraint Violating Yak Guru

USA
269 Posts

Posted - 01/12/2010 :  09:56:55  Show Profile  Send DP978 an AOL message  Click to see DP978's MSN Messenger address  Reply with Quote
Iaint, try soemthing like...

SELECT TOP 100 *, LEFT(PostCode, 3) as TrimmedPC
FROM Addresses
WHERE RTrim(LTrim(PostCode)) LIKE '[A-Z][A-Z][0-9]'

Edit: Using a Select * will never change the values in the columns, you will either have to accept the field at the end or write out the entire SQL code for the fields you want

Edited by - DP978 on 01/12/2010 09:57:49
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/12/2010 :  12:10:54  Show Profile  Reply with Quote
"Thanks for your help guys, but I don't think we're there yet."

OK ...

Does this get you something:

SELECT ']' + adrZipCode + '['
FROM Addresses
WHERE adrZipCode = 'BA9'

If not then although the data looks like BA9 it isn't those actual characters (may be extended / UNICODE character set or some other grief )

If that works what about:

SELECT ']' + adrZipCode + '['
FROM Addresses
WHERE adrZipCode = 'BA9'
      AND adrZipCode LIKE 'BA9'

then

      AND adrZipCode LIKE '[B][A][9]'

and so on until hopefully you hit on the cause

I don't think you can globally change the Escape character in LIKE expressions, I think you have to explicitly use:

      AND adrZipCode LIKE '\[A-Z\]' ESCAPE '\'

but if it is possible to change the ESCAPE character maybe someone has done that - and made it "[" or something equally useless!!
Go to Top of Page

DP978
Constraint Violating Yak Guru

USA
269 Posts

Posted - 01/12/2010 :  12:18:55  Show Profile  Send DP978 an AOL message  Click to see DP978's MSN Messenger address  Reply with Quote
Kristen, I was under the impression his return values just were not in the format he wanted...not that the actual rows were wrong...
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/12/2010 :  12:40:07  Show Profile  Reply with Quote
Ah, OK. I didn't read it like that:

"As another example, this returns no rows:

SELECT
*
FROM
Addresses
WHERE
adrZipCode LIKE '[A-Z]%' AND
LEFT(adrZipCode, 3) = 'BA9'
"

I figure that ought to work given that he says:

"This returns 18 rows though, all starting with or equal to BA9:

SELECT
*
FROM
Addresses
WHERE
adrZipCode LIKE '%' AND
LEFT(adrZipCode, 3) = 'BA9'
"

and actually if I'd ready that more carefully the examples I gave him to try may be somewhat moot.

Edited by - Kristen on 01/12/2010 12:41:02
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 01/13/2010 :  01:13:01  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by IainT

Hey,


Webfred - the value is exactly BA9. No more no less.


madhivanan - the function you give here would also return

"BA9 The rest of this field is rubbish"

but I only want the exact value "BA9"


Kristen - it is a varchar(15).


Thanks for your help guys, but I don't think we're there yet.

As another example, this returns no rows:

SELECT
*
FROM
Addresses
WHERE
adrZipCode LIKE '[A-Z]%' AND
LEFT(adrZipCode, 3) = 'BA9'


This returns 18 rows though, all starting with or equal to BA9:

SELECT
*
FROM
Addresses
WHERE
adrZipCode LIKE '%' AND
LEFT(adrZipCode, 3) = 'BA9'



It seems to have a problem with square brackets at the start of the like.

Thanks
Iain


Can you post some sample data?
It would be easier to see where it goes wrong

Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 01/13/2010 01:14:14
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/13/2010 :  02:31:45  Show Profile  Reply with Quote
Madhi, as I understand it the sample data is a varchar(15) column with the value 'BA9'

(however, I'd appreciate results from my proposed tests to see if that value can actually be found with an "equals test" - ruling out some additional extraneous extended characters or somesuch that don't show up on-screen
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 01/13/2010 :  03:53:12  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
I dont see anything wrong here

SELECT
*
FROM
(
select cast('test' as varchar(15)) as adrZipCode union all
select 'BA9' union all
select 'BA99'
) as Addresses
WHERE
adrZipCode LIKE '[A-Z]%' AND
LEFT(adrZipCode, 3) = 'BA9'


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/13/2010 :  04:06:31  Show Profile  Reply with Quote
I think OP is missing something here. I too suspect it may be do to some unprintable characters as Kristen said
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/13/2010 :  08:14:43  Show Profile  Reply with Quote
Case sensitive collation on column / database perhaps? (but I see the data "BA9" and the test LIKE '[A-Z][A-Z][0-9]' - so unless the postcode is "ba9" or the test LIKE '[a-z][a-z][0-9]' this is a red herring)
Go to Top of Page

IainT
Starting Member

12 Posts

Posted - 01/13/2010 :  08:22:57  Show Profile  Reply with Quote
Yeah, unprintable characters is the only thing I can see as well, but I really don't know how.

I am utterly utterly confused now. The following three statements all return the 1 row I expected:

SELECT
*
FROM
Addresses
WHERE
adrZipCode = 'BA9'

SELECT
*
FROM
Addresses
WHERE
adrZipCode LIKE 'BA9'

SELECT
*
FROM
Addresses
WHERE
adrZipCode LIKE '[B]A9'



Each of these statements comes up with the one row I was expecting.

This however comes up with no results:

SELECT
*
FROM
Addresses
WHERE
adrZipCode LIKE '[A-Z]A9'


Why would:

LIKE '[A-Z]A9'

not work, but

LIKE '[B]A9'

works without any trouble.

I am sure that I'm not using a weird character for B. I even copied and pasted B from "SQL Server Books" at the top of this page to make sure that it's not coming up with a weird character when I press B on my keyboard.

For some reason it's not accepting B as part of the range [A-Z]. Does anyone have any idea why this might be?

Thanks for the suggestions by the way guys. I really appreciate it!

Iain
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 01/13/2010 :  08:29:22  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
See what this return

SELECT
*
FROM
Addresses
WHERE
adrZipCode not LIKE '[A-Z0-9]%'

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

IainT
Starting Member

12 Posts

Posted - 01/13/2010 :  08:32:57  Show Profile  Reply with Quote
What the hell?!

This works:

SELECT
*
FROM
Addresses
WHERE
adrZipCode LIKE '[A-Y]A9'


and this doesn't:

SELECT
*
FROM
Addresses
WHERE
adrZipCode LIKE '[A-Z]A9'



These are the only results from that query madhivanan:

$X4 8EL
)X12 9BG
**********
.
\TQ13 0BE

The row in question isn't there. Just the rubbish filling the database.

Thanks
Iain
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/13/2010 :  08:42:52  Show Profile  Reply with Quote
whats collation setting of your db?
Go to Top of Page

DP978
Constraint Violating Yak Guru

USA
269 Posts

Posted - 01/13/2010 :  08:43:26  Show Profile  Send DP978 an AOL message  Click to see DP978's MSN Messenger address  Reply with Quote
DO you get the right answer when you put '[ABCD]A9', I assume you would but just checking.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/13/2010 :  08:47:03  Show Profile  Reply with Quote
Well ... clutching at straws here. Lets check that the Z you are getting is the one we are all expecting it to be :)

SET NOCOUNT ON

DECLARE @strSQL varchar(50),
        @intLoop int

SELECT @strSQL = '[A-Z]'

SELECT @intLoop = 1	-- Force first iteration

WHILE @intLoop <= LEN(@strSQL)
BEGIN
	SELECT	@intLoop, ':', ASCII(SUBString(@strSQL, @intLoop, 1)), '-', SUBString(@strSQL, @intLoop, 1)
	SELECT	@intLoop = @intLoop + 1
END

SET NOCOUNT OFF

You might want to cut & paste your own [A-Z], from a failed query, into the code rather than using mine (because mine works here!!)

For reference here's what I get :

----------- ---- ----------- ---- ---- 
1           :    91          -    [

----------- ---- ----------- ---- ---- 
2           :    65          -    A

----------- ---- ----------- ---- ---- 
3           :    45          -    -

----------- ---- ----------- ---- ---- 
4           :    90          -    Z

----------- ---- ----------- ---- ---- 
5           :    93          -    ]
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/13/2010 :  08:48:46  Show Profile  Reply with Quote
"whats collation setting of your db?"

Good point. I'll add: What collation for the column [adrZipCode] ?
Go to Top of Page
Page: of 4 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000