asked
I have one column. Its having a,aa,aaaa,bbb,aab,aac values. I want to filter the row only a or aa or aaa values.
Pls help. How can i get the exact result??
This site runs off of voting. If any of the answers below are helpful, you can vote them up by clicking on the thumbs up next to them. Also, you need to identify the best answer by clicking on the check mark next to it.
You'll have to split the column then filter using the values you split out. You can learn more about string splitting with a tally table is Jeff Moden's Article - [
http://www.sqlservercentral.com/articles/T-SQL/62867/][1] Erland Sommarskog also has an exhaustive analysis of various string splitting approaches - [
http://www.sommarskog.se/arrays-in-sql-2005.html and
http://www.sommarskog.se/arrays-in-sql.html][2] [1]:
http://www.sqlservercentral.com/articles/T-SQL/62867/ [2]:
http://www.sommarskog.se/arrays-in-sql-2005.html+and+http://www.sommarskog.se/arrays-in-sql.html
Assuming you're telling us that each of the values in your list is in a separate row. The answer is: SELECT Column, list? FROM Table WHERE Column IN ('a', 'aa', 'aaa') If this is incorrect then "KenJ" has the right answer.
If its like you want to get the column data which ends with 'a', select * from TableName where Column like '%a'
An alternative to @dvroman's suggestion, if you *know* that your data only has alphabetical characters in, is to do: SELECT Column FROM Table WHERE Column LIKE '%a%' AND Column NOT LIKE '%[b-z]%' Here, we're making more interesting use of wildcards. Have a look at the MSDN documentation for [`LIKE`][1] for even more on wildcards. Of course, there are all sorts of potential issues with this one. Collations, case sensitivity, what if there is non-alpha data... [1]:
http://technet.microsoft.com/en-us/library/ms179859.aspx
19 People are following this question.
