Skip to main content
J
jkgiven
Inspiring
May 25, 2007
Answered

How to exclude characters

  • May 25, 2007
  • 10 replies
  • 765 views
Within my query, I have a WHERE statement:

WHERE Left(t_item,2) NOT LIKE '%-'
AND Left(t_item,2) NOT LIKE '_%'

This was to exclude the underscore character. I was told this was the only non number or letter used. Of course now I am finding out that this is not true and many other characters are used. Is there a way for me to alter this to say that the left 2 character MUST be either a letter or a number.
    This topic has been closed for replies.
    Correct answer paross1
    Doesn't look like you can't refer to your column alias in your WHERE clause.

    SELECT DISTINCT Left(t_item,2) AS series
    FROM dbo.ttiitm001#companyNumber#
    WHERE Left(t_item,1) IN ('0','1','2',...'O','P','Q','R','S','T','U','V','W','X','Y','Z')
    AND Right(Left(t_item,2),1) IN ('0','1','2',...'O','P','Q','R','S','T','U','V','W','X','Y','Z')
    ORDER BY 1

    --or perhaps--

    SELECT DISTINCT Left(t_item,2) AS series
    FROM dbo.ttiitm001#companyNumber#
    WHERE Left(t_item,1) IN ('0','1','2',...'O','P','Q','R','S','T','U','V','W','X','Y','Z')
    AND Substring(t_item,2,1) IN ('0','1','2',...'O','P','Q','R','S','T','U','V','W','X','Y','Z')
    ORDER BY 1

    --or--

    SELECT DISTINCT Left(t_item,2) AS series
    FROM dbo.ttiitm001#companyNumber#
    WHERE Left(t_item,1) IN ('0','1','2',...'O','P','Q','R','S','T','U','V','W','X','Y','Z')
    AND Mid(t_item,2,1) IN ('0','1','2',...'O','P','Q','R','S','T','U','V','W','X','Y','Z')
    ORDER BY 1

    Phil

    10 replies

    J
    jkgivenAuthor
    Inspiring
    June 1, 2007
    Thanks Paross1 - that Right(Left(t_item,2),1) was just what I was looking for.
    J
    jkgivenAuthor
    Inspiring
    May 31, 2007
    bump
    J
    jkgivenAuthor
    Inspiring
    May 29, 2007
    When I took out the "AND Right(..." line it told me it was an invalid column name. So I replaced the "series" in the WHERE line and replaced it with the t_item (as shown in the SELECT line) and left the AND line out. This worked as for what it is, but I need to make sure the second character is also in the list of acceptable characters (meaning numbers or letters only).

    I thought the SELECT DISTINCT Left(t_item,2) AS series would give me only the first two characters and thus I could use the LEFT(series,1) and the RIGHT(series,1)... I guess I just don't understand.
    P
    paross1Correct answer
    Participating Frequently
    May 31, 2007
    Doesn't look like you can't refer to your column alias in your WHERE clause.

    SELECT DISTINCT Left(t_item,2) AS series
    FROM dbo.ttiitm001#companyNumber#
    WHERE Left(t_item,1) IN ('0','1','2',...'O','P','Q','R','S','T','U','V','W','X','Y','Z')
    AND Right(Left(t_item,2),1) IN ('0','1','2',...'O','P','Q','R','S','T','U','V','W','X','Y','Z')
    ORDER BY 1

    --or perhaps--

    SELECT DISTINCT Left(t_item,2) AS series
    FROM dbo.ttiitm001#companyNumber#
    WHERE Left(t_item,1) IN ('0','1','2',...'O','P','Q','R','S','T','U','V','W','X','Y','Z')
    AND Substring(t_item,2,1) IN ('0','1','2',...'O','P','Q','R','S','T','U','V','W','X','Y','Z')
    ORDER BY 1

    --or--

    SELECT DISTINCT Left(t_item,2) AS series
    FROM dbo.ttiitm001#companyNumber#
    WHERE Left(t_item,1) IN ('0','1','2',...'O','P','Q','R','S','T','U','V','W','X','Y','Z')
    AND Mid(t_item,2,1) IN ('0','1','2',...'O','P','Q','R','S','T','U','V','W','X','Y','Z')
    ORDER BY 1

    Phil
    D
    Daverms
    Inspiring
    May 29, 2007
    Hi,

    Can't you able to execute any help command in your console?...
    D
    Dan_Bracuk
    Inspiring
    May 29, 2007
    start with no where clause. Does the query return anything? Do any of the records have upper case letters or numbers in them?

    If so, add the 1st part of your where clause. What happens then?
    J
    jkgivenAuthor
    Inspiring
    May 29, 2007
    So now I was trying it this way - but it doesn't return anything.

    SELECT DISTINCT Left(t_item,2) AS series
    FROM dbo.ttiitm001#companyNumber#
    WHERE Left(series,1) IN ('0','1','2',...'O','P','Q','R','S','T','U','V','W','X','Y','Z')
    AND Right(series,1) IN ('0','1','2',...'O','P','Q','R','S','T','U','V','W','X','Y','Z')
    ORDER BY series
    D
    Daverms
    Inspiring
    May 29, 2007
    Hi,

    Can you able to go through the supported String functions of your propriatary DB?...
    J
    jkgivenAuthor
    Inspiring
    May 29, 2007
    The DB is some propriatary DB that I don't know anything about. Let's say it is similar to SQL. It doesn't like "substr"
    N
    Newsgroup_User
    Inspiring
    May 25, 2007
    When asking DB-centric questions, it's always helpful if you tell us WHICH
    DB you're using.

    --
    Adam
    D
    Dan_Bracuk
    Inspiring
    May 25, 2007
    different db's have different string functions. I'll use oracle syntax as an example. It should give you the general idea.

    where substr(yourfield, 1, 1) in ('0','1', ..., 'z','Z')
    and substr(yourfield, 2, 1) in ('0','1', ..., 'z','Z')


    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices