Wildcards - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Wildcards

Author

Topic

IainT
Starting Member

12 Posts

Posted - 2010-01-12 : 07:49:47

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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-12 : 07:55:54

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.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-12 : 07:56:04

It should be

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

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-01-12 : 08:31:45

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.

IainT
Starting Member

12 Posts

Posted - 2010-01-12 : 09:53:14

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

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-12 : 09:56:55

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

Kristen
Test

22859 Posts

Posted - 2010-01-12 : 12:10:54

"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!!

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-12 : 12:18:55

Kristen, I was under the impression his return values just were not in the format he wanted...not that the actual rows were wrong...

Kristen
Test

22859 Posts

Posted - 2010-01-12 : 12:40:07

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.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-13 : 01:13:01

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

Kristen
Test

22859 Posts

Posted - 2010-01-13 : 02:31:45

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-13 : 03:53:12

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 04:06:31

I think OP is missing something here. I too suspect it may be do to some unprintable characters as Kristen said

Kristen
Test

22859 Posts

Posted - 2010-01-13 : 08:14:43

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)

IainT
Starting Member

12 Posts

Posted - 2010-01-13 : 08:22:57

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-13 : 08:29:22

See what this return

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

Madhivanan

Failing to plan is Planning to fail

IainT
Starting Member

12 Posts

Posted - 2010-01-13 : 08:32:57

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 08:42:52

whats collation setting of your db?

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-13 : 08:43:26

DO you get the right answer when you put '[ABCD]A9', I assume you would but just checking.

Kristen
Test

22859 Posts

Posted - 2010-01-13 : 08:47:03

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          -    ]

Kristen
Test

22859 Posts

Posted - 2010-01-13 : 08:48:46

"whats collation setting of your db?"

Good point. I'll add: What collation for the column [adrZipCode] ?

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources