Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2010
    Posts
    2

    Unanswered: Need help to create a query

    I am trying to create query to pull if the letter in 'B', 'l', 'a', 'W'.

    In short query should able to find any combination in uppercase or lower case from one table and one specific column.

    Thanks in advance

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT *
       FROM [foo]
       WHERE 0 < PatIndex(('&#37;' + [foo].[bar] + '%')
          COLLATE SQL_Latin1_General_CP1_CI_AS, 'BlaW')
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2010
    Posts
    2
    the table is id_password and column is password.

    I tried above query but it didn't work. Can you use above table and column name and re-write the query.

    Thanks

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You enforce one letter passwords and store them in clear text?
    That's...novel.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I assume your password can actually be longer than one character.
    Code:
    USE test
    GO 
    
    CREATE TABLE foo
        (
            bar     CHAR(6) COLLATE LATIN1_GENERAL_BIN
        )
        
    INSERT  foo
    VALUES  ('pootle')
          , ('flump')
          , ('R937')
    
    DECLARE @lettergroup    AS VARCHAR(26)  = 'BlaW'
    
    SELECT  DISTINCT foo.bar
    FROM    dbo.numbers
    INNER JOIN
            dbo.foo
    ON  0 < CHARINDEX(SUBSTRING(UPPER(@lettergroup), numbers.number, 1), foo.bar)
            + CHARINDEX(CHAR(ASCII(SUBSTRING(UPPER(@lettergroup), numbers.number, 1)) + 32), foo.bar)
    WHERE   numbers.number BETWEEN 1 AND LEN(@lettergroup)
    
    DROP TABLE foo
    Like Pat's solution, this allows the parameterisation of your test characters however allows two strings of length greater than one to be compared by exploding the test string.

    EDIT - in case you don't have a numbers table:
    http://sqlblog.com/blogs/adam_machan...ers-table.aspx

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump View Post
    That's...novel.
    the king of understatement strikes again

    i bet what's really going on here has been "disguised"

    i can't work on those problems any more, because they always morph around crazily after the first post
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •