question

balu.arunkumar avatar image
balu.arunkumar 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??
t-sql
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
KenJ avatar image
KenJ answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dvroman avatar image
dvroman answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

that would make for an interesting use of commas. Now I'm truly curious to hear back from the OP :)
0 Likes 0 ·
minijebu avatar image
minijebu answered
If its like you want to get the column data which ends with 'a', select * from TableName where Column like '%a'
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank u so much Thomas...
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.