Alphanumberic string search

Question

Alphanumberic string search

sql_ques

SSCommitted

Points: 1546
More actions
February 2, 2010 at 7:55 am

#215217

Hi All,
Here is a query,
SELECT LASTNAME FROM CPM.dbo.T_CANADIAN
WHERE '678567Z' BETWEEN '673001P' AND '679000P'
It should not display any result as the where value numerials fall in between '673001P' AND '679000P', but the value is '678567Z' , since the last character is 'Z', it should not diaplay any result, but it still displays values when i execute the query.
Someone help.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

BrainDonor SSCoach Points: 19254 More actions · Answer 1

In which case you need to check the right-most character of the value first, and then if that is within range you can check the numeric part.

BrainDonor.

Steve Hall
Linkedin
Blog Site

sql_ques SSCommitted Points: 1546 More actions · Answer 2

sql_ques

SSCommitted

Points: 1546

February 2, 2010 at 8:07 am

#1112293

Please tell me how to do it.

WayneS SSC Guru Points: 95460 More actions · Answer 3

sql_ques (2/2/2010)
Please tell me how to do it.

Look in BOL at the LEFT, RIGHT and SUBSTRING functions.

It looks like the root issue here is a single data field with multiple pieces of data... why else would it matter that you have to search for stuff ending in a range where it ENDS with the same. It sounds like this field needs to be broken down into two fields.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

BrainDonor SSCoach Points: 19254 More actions · Answer 4

sql_ques (2/2/2010)
Please tell me how to do it.

No.

I will however take as long as is required to help you to work out how to write it. That is how you learn the more fundamental aspects of SQL Server. I can be very patient, as long as you don't give in, as you did with:

http://www.sqlservercentral.com/Forums/Topic837177-8-1.aspx

and

http://www.sqlservercentral.com/Forums/Topic835685-1291-1.aspx

There are three commands of interest - LEFT, RIGHT and SUBSTRING (probably using LEN, if these values have different lengths).

Have a look at them and give it some thought. Come back with an attempt at the solution and we'll be happy to help.

Steve Hall
Linkedin
Blog Site

sql_ques SSCommitted Points: 1546 More actions · Answer 5

SELECT LASTNAME FROM CPM.dbo.T_CANADIAN

WHERE '678567' BETWEEN '673001' AND '679000'

AND 'Z' BETWEEN 'P' AND 'P'

this worked.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 6

Would this work in all cases? Why dont you try to use the functions that we re hinted to you by others?

-Roy

BrainDonor SSCoach Points: 19254 More actions · Answer 7

sql_ques (2/2/2010)
SELECT LASTNAME FROM CPM.dbo.T_CANADIAN
WHERE '678567' BETWEEN '673001' AND '679000'
AND 'Z' BETWEEN 'P' AND 'P'
this worked.

Indeed it will. However, you've had to split the values yourself - originally it was

WHERE '678567Z' BETWEEN '673001P' AND '679000P'

Assuming '678567Z' and the others are actually column values within the database, how would you get SS to spilt them within the code? You need to replicate the result that you've achieved, but by splitting the string via SQL statements.

brainDonor.

Steve Hall
Linkedin
Blog Site

WayneS SSC Guru Points: 95460 More actions · Answer 8

Roy Ernest (2/2/2010)
Would this work in all cases? Why dont you try to use the functions that we re hinted to you by others?

Those were hints? Heck, IMHO, those were just 1 short step shy of the answer itself.:w00t:

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 9

sql_ques (2/2/2010)
Hi All,
Here is a query,
SELECT LASTNAME FROM CPM.dbo.T_CANADIAN
WHERE '678567Z' BETWEEN '673001P' AND '679000P'
It should not display any result as the where value numerials fall in between '673001P' AND '679000P', but the value is '678567Z' , since the last character is 'Z', it should not diaplay any result, but it still displays values when i execute the query.
Someone help.

Based on the OP history of deleting posts, I just wanted to keep it around for prosteriety (sp?).

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

LutzM SSC Guru Points: 107049 More actions · Answer 10

sql_ques (2/2/2010)
SELECT LASTNAME FROM CPM.dbo.T_CANADIAN
WHERE '678567' BETWEEN '673001' AND '679000'
AND 'Z' BETWEEN 'P' AND 'P'
this worked.

I actually doubt that this query would return anything but an empty result set.

Reason:

The WHERE part "AND 'Z' BETWEEN 'P' AND 'P'" will always be false since 'Z' will never be between 'P' and 'P'.

Therefore, the whole WHERE condition will result on zero matching rows.

Just wondering...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

lmu92 (2/2/2010)
sql_ques (2/2/2010)
SELECT LASTNAME FROM CPM.dbo.T_CANADIAN
WHERE '678567' BETWEEN '673001' AND '679000'
AND 'Z' BETWEEN 'P' AND 'P'
this worked.
I actually doubt that this query would return anything but an empty result set.
Reason:
The WHERE part "AND 'Z' BETWEEN 'P' AND 'P'" will always be false since 'Z' will never be between 'P' and 'P'.
Therefore, the whole WHERE condition will result on zero matching rows.
Just wondering...

That is my inclination as well. I don't see how this evaluates to anything and then returns a valid result-set.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

WayneS SSC Guru Points: 95460 More actions · Answer 12

And yet, if you look at the last line of the OPs original post, this is exactly what he wants.... for now.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

LutzM SSC Guru Points: 107049 More actions · Answer 13

WayneS (2/2/2010)
And yet, if you look at the last line of the OPs original post, this is exactly what he wants.... for now.

So, do you think having a persisted calculated column that moves the last character to the first position of the value would be one of the possible answers that even might help performance?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

lmu92 (2/2/2010)
sql_ques (2/2/2010)
SELECT LASTNAME FROM CPM.dbo.T_CANADIAN
WHERE '678567' BETWEEN '673001' AND '679000'
AND 'Z' BETWEEN 'P' AND 'P'
this worked.
I actually doubt that this query would return anything but an empty result set.
Reason:
The WHERE part "AND 'Z' BETWEEN 'P' AND 'P'" will always be false since 'Z' will never be between 'P' and 'P'.
Therefore, the whole WHERE condition will result on zero matching rows.
Just wondering...

Why not just do

SELECT LASTNAME FROM CPM.dbo.T_CANADIAN

WHERE 1 <> 2

That should provide an empty result set too.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events