Using SELECT LEFT and WHERE IN in the same SELECT Statement.

  • MarkW.Rhythm1

    SSC Enthusiast

    Points: 163

    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

  • Jonathan AC Roberts

    SSCoach

    Points: 16879

    WHERE AKey IN ('AAA', 'BBB', 'CCC', 'DDD', 'EEE', 'GGG', 'JJJ') AND LEN(AKey) = 9

    All 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.

     

  • ScottPletcher

    SSC Guru

    Points: 98115

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88110

    WHERE LEFT(AKey, 3) IN ('AAA', 'BBB', 'CCC', 'DDD', 'EEE', 'GGG', 'JJJ') AND LEN(AKey) = 9

     

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • MarkW.Rhythm1

    SSC Enthusiast

    Points: 163

    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)

  • MarkW.Rhythm1

    SSC Enthusiast

    Points: 163

    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

  • Phil Parkin

    SSC Guru

    Points: 243681

    Jeffrey Williams 3188 wrote:

    WHERE LEFT(AKey, 3) IN ('AAA', 'BBB', 'CCC', 'DDD', 'EEE', 'GGG', 'JJJ') AND LEN(AKey) = 9

    AFAIK, 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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88110

    Phil Parkin wrote:

    Jeffrey Williams 3188 wrote:

    WHERE LEFT(AKey, 3) IN ('AAA', 'BBB', 'CCC', 'DDD', 'EEE', 'GGG', 'JJJ') AND LEN(AKey) = 9

    AFAIK, 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]'

     

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • ScottPletcher

    SSC Guru

    Points: 98115

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • scdecade

    Old Hand

    Points: 394

    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(
    akey nvarchar(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(
    @key nvarchar(12),
    @key_len int,
    @repeat_count int,
    @chars nvarchar(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

  • ScottPletcher

    SSC Guru

    Points: 98115

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

Viewing 11 posts - 1 through 11 (of 11 total)

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