October 9, 2019 at 7:40 pm
Greetings ~
I'm trying to get the following query to work in SQL 2014
[Code]
SELECT LEFT(AKey, 3) AS Foo
FROM dbo.AKey
WHERE AKey IN ('AAA', 'BBB', 'CCC', 'DDD', 'EEE', 'GGG', 'JJJ') AND LEN(AKey) = 9
[/Code]
The issue isn't that the query throws an error - but rather it returns an empty set - and yes, each of the values listed in the WHERE Clause do exist in the AKey Table
October 9, 2019 at 7:48 pm
WHERE AKey IN ('AAA', 'BBB', 'CCC', 'DDD', 'EEE', 'GGG', 'JJJ') AND LEN(AKey) = 9All the values in the IN have a length of 3. But you are saying that the length must also be 9.
That's something that can never be true.
October 9, 2019 at 8:10 pm
You probably need something like this:
WHERE (AKey LIKE 'AAA%' OR AKey LIKE 'BBB%' OR AKey LIKE 'CCC%' OR AKey LIKE 'DDD%' OR
AKey LIKE 'EEE%' OR AKey LIKE 'GGG%' OR AKey LIKE 'JJJ%') AND LEN(AKey) = 9
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 9, 2019 at 8:11 pm
WHERE LEFT(AKey, 3) IN ('AAA', 'BBB', 'CCC', 'DDD', 'EEE', 'GGG', 'JJJ') AND LEN(AKey) = 9
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 9, 2019 at 8:18 pm
Ahhh
SS your question inspired ~
I changed my original query to the following:
[Code]
SELECT AKey
FROM dbo.AKey
WHERE LEFT(AKey, 3) IN ('AAA', 'BBB', 'CCC', 'DDD', 'EEE', 'GGG', 'JJJ') AND LEN(AKey) = 9
[/Code]
This works perfectly!
What I was trying to tell SQL to do is: Return all AKeys which have as their first 3 letters either 'AAA' or 'BBB' or 'CCC' and so on...and where the entire length of the AKey is 9 characters. - Not sure what I was actually telling SQL to do with my first attempt.
Thanks again SS
(Can someone share how to create code windows - What I trying is clearly not working - Thanks)
October 9, 2019 at 8:23 pm
Geez ~ Sorry - I'm new to the forum and thought SSCoach was a nickname...
Thank you Jonathan, Scott & Jeffrey for helping me with this
Mark
October 9, 2019 at 8:31 pm
WHERE LEFT(AKey, 3) IN ('AAA', 'BBB', 'CCC', 'DDD', 'EEE', 'GGG', 'JJJ') AND LEN(AKey) = 9AFAIK, Scott's solution is the better one, because LIKE 'AAA%' is potentially SARGable - but throwing in the LEFT() function removes that potential.
Or maybe the engine is now sophisticated enough now to handle both optimally.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 9, 2019 at 9:41 pm
Jeffrey Williams wrote:WHERE LEFT(AKey, 3) IN ('AAA', 'BBB', 'CCC', 'DDD', 'EEE', 'GGG', 'JJJ') AND LEN(AKey) = 9AFAIK, Scott's solution is the better one, because LIKE 'AAA%' is potentially SARGable - but throwing in the LEFT() function removes that potential.
Or maybe the engine is now sophisticated enough now to handle both optimally.
Depends on whether or not that column is indexed, how many rows meet the requirement and what other columns are included in the query. I wouldn't be surprised if this utilized a clustered index scan regardless of there being an index available on Akey.
Another approach would be:
WHERE AKey LIKE 'AAA[0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z]'
OR AKey LIKE 'BBB[0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z]'
OR AKey LIKE 'CCC[0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z]'
OR AKey LIKE 'DDD[0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z]'
OR AKey LIKE 'EEE[0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z]'
OR AKey LIKE 'GGG[0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z]'
OR AKey LIKE 'JJJ[0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z]'
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 9, 2019 at 10:17 pm
And that's why I wrote the code the way I did. The other way is somewhat easier to code, but potentially far worse in performance.
The underlying rule is:
Never use a function on a table column if it can at all be reasonably avoided.
By extension, that means you never use ISNULL() in a WHERE, since it can be avoided. Thus, you should write:
WHERE (column_any IS NULL OR column_any = 'A') --correct
rather than:
WHERE ISNULL(column_any, 'A') = 'A' --incorrect, although I see this all the time in peoples' code
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 10, 2019 at 1:54 pm
Here's a tvf named 'left_chars' that attempts to generalize all of the requirements. Sorry I'm learning about APPLY (new hammer) so everything looks like a nail. The function should work for: 1) any key length (less than 12), for 2) any number of repeated characters, for 3) any list of characters (uses a json array as input) at the beginning of an nvarchar(12) string. Fwiw:
drop table if exists test;
go
create table test(
akeynvarchar(12));
go
insert test values
('aaabbbccczzz'),
('bbbbbbccc'),
('aaabbbccc'),
('!!abbbccc'),
('!!!bbbccc'),
('dddbbbccc'),
('asabbbccc'),
('cccbbbccc'),
('zzzzbbccc'),
('aaxbbbccc');
go
drop function if exists dbo.left_chars;
go
create function dbo.left_chars(
@keynvarchar(12),
@key_lenint,
@repeat_countint,
@charsnvarchar(max))
returns table as
return
select 1 a
from
openjson(@chars, N'strict $.chars')
where
len(@key)=@key_len
and @key like replicate([value], @repeat_count)+'%';
go
select
a.akey
from
test a
cross apply
dbo.left_chars(a.akey, 9, 3, N'{ "chars": ["!", "B", "C", "D", "Z"] }') x
To return keys with 10 characters that begin with 5 q's then the parameters to cross apply with would be:
dbo.left_chars(a.akey, 10, 5, N'{ "chars": ["Q"] }')To return keys with 9 characters that begin with 2 '!', or 'a', or 'z' then the parameters to cross apply with would be:
dbo.left_chars(a.akey, 9, 2, N'{ "chars": ["!", "A", "Z"] }')
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 10, 2019 at 5:36 pm
This might give better performance, if the optimizer recognizes the chance:
WHERE AKey LIKE '[ABCDEGJ]%' AND (AKey LIKE 'AAA%' OR AKey LIKE 'BBB%' OR AKey LIKE 'CCC%' OR AKey LIKE 'DDD%' OR AKey LIKE 'EEE%' OR AKey LIKE 'GGG%' OR AKey LIKE 'JJJ%') AND LEN(AKey) = 9
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply