• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

SQL to restrict rows by first letter / number

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    #11
    Actually, I was thinking that the SQL using the OR and LIKE and SUBSTR is not going to be very efficient and is going to scan the data.

    I am thinking of maintaining another column (via a trigger) to contain the first letter of the band name and if it is not a letter, store # in the field, so the SQL becomes:

    SELECT band_name
    FROM bands
    WHERE band_name_begins = @p

    And put an index on this column, as the data is rarely updated or added to, but read A LOT.

    Comment


      #12
      There are no good bands which start with a number anyway. I'd just dump them all if I was you.

      Comment


        #13
        Apart from 3 Mustaphas 3.

        Comment


          #14
          Originally posted by DimPrawn View Post
          That's in module 2, to be learned next week.
          You are Bob Shawadiwadi AICMFP

          Originally posted by DimPrawn View Post
          Actually, I was thinking that the SQL using the OR and LIKE and SUBSTR is not going to be very efficient and is going to scan the data.

          I am thinking of maintaining another column (via a trigger) to contain the first letter of the band name and if it is not a letter, store # in the field, so the SQL becomes:

          SELECT band_name
          FROM bands
          WHERE band_name_begins = @p

          And put an index on this column, as the data is rarely updated or added to, but read A LOT.
          Steady now, you'll start to look like a professional (and I don't mean Bodie or Doyle).
          ‎"See, you think I give a tulip. Wrong. In fact, while you talk, I'm thinking; How can I give less of a tulip? That's why I look interested."

          Comment


            #15
            How many bands are you tracking? 100K? 1M? wow!

            Besides you could just make a function index in oracle, or calculated column in SQL server and index it.
            McCoy: "Medical men are trained in logic."
            Spock: "Trained? Judging from you, I would have guessed it was trial and error."

            Comment


              #16
              Actually it's not bands. I just used that as an example.

              Comment


                #17
                Originally posted by DimPrawn View Post
                Actually it's not bands. I just used that as an example.
                This is why one should use metasynctactic variables:
                "Say you want to select all the rows in table "foo" having a value in the varchar field "bar" beginning with a specific character from a particular range of characters, or beginning with any character not in that range if some wildcard character is specified..."

                That way people don't get distracted talking about nonsense like "How many bands are there in the world" and suchlike - they remain focused on solving the specific problem for the abstract case.

                Comment


                  #18
                  Have you considered...

                  SELECT band_name
                  FROM bands
                  WHERE band_name like (replace(@p,'#','[0-9]')+'%')

                  Comment


                    #19
                    Originally posted by NickFitz View Post
                    This is why one should use metasynctactic variables:
                    "Say you want to select all the rows in table "foo" having a value in the varchar field "bar" beginning with a specific character from a particular range of characters, or beginning with any character not in that range if some wildcard character is specified..."

                    That way people don't get distracted talking about nonsense like "How many bands are there in the world" and suchlike - they remain focused on solving the specific problem for the abstract case.
                    I tend to create tables called "Select" with fields called "From" and "Order By".

                    It confuses the hell out of everyone else though.

                    Comment


                      #20
                      Originally posted by EvilWeevil View Post
                      Have you considered...

                      SELECT band_name
                      FROM bands
                      WHERE band_name like (replace(@p,'#','[0-9]')+'%')
                      No. A good concise answer.

                      Comment

                      Working...
                      X