0

I have a table which has a column with values which are separated by commas.

For example the Column has following values:

index Column
1     X,Y,Z,A,B,C,D,E,F,G,H,I,J,K,L,M,T,W,O
2     Y,Z,G,H,I,J,K,L,M,T,E,W,O
3     X,Y,Z,F,G,H,I,L,M,T,W,O,A,B,C,D,E,J,K
4     A,Z,B,X,Y
5     X,Y,A,Z,B

I want to print data of the Column which has X,Y,Z,A,B,C,D,E,F,G,H,I,J,K,L,M,T,W,O only so that can be index number 1 as well as 3 as they have the same values just in different order. Cant search for all values manually because if the requirement to search the query changes to say X,Y,A,Z,B only then it has to be index number 4 and 5. How do I do that?. Thanks!

Rachana Gandhi
  • 66
  • 10
  • 2
    What database do you use ? – VBoka Feb 14 '20 at 06:14
  • I use SSMS so its an RDBMS – Rachana Gandhi Feb 14 '20 at 06:19
  • https://techterms.com/definition/rdbms – VBoka Feb 14 '20 at 06:25
  • How many values are you actually interested in? Your example shows 5. What is the actual number? – urdearboy Feb 14 '20 at 06:31
  • There are more than 20 values, I changed the example – Rachana Gandhi Feb 14 '20 at 06:33
  • I did and what @VBoka said is true. If the search query changes to say X,Y,A,Z,B then it will return all the rows which has those values along with other which i dont want. which in this case would be 1,3,5 but I only want 5 – Rachana Gandhi Feb 14 '20 at 06:58
  • 2
    Rreopen this question if you can and help @RachanaGandhi – VBoka Feb 14 '20 at 06:59
  • 1
    The table is a relational anti-pattern, have you considered a table with index-column-value rows? Moreover if column is a DB column & value its value in row then it's likely this is part of a larger sometimes anti-pattern EAV. – philipxy Feb 14 '20 at 07:22
  • @RachanaGandhi: "SSMS" is not a "RDBMS" it's a SQL client application that connects to Microsoft SQL Server –  Feb 14 '20 at 07:27
  • 2
    [Turning a Comma Separated string into individual rows](https://stackoverflow.com/q/5493510/3404097) – philipxy Feb 14 '20 at 07:29
  • Thankyou @philipxy that can be one solution but I already have a database which has rows more than 20k. This is going to make it a lot bigger. Is there another solution that I can use? – Rachana Gandhi Feb 14 '20 at 07:36
  • Thanks for pointing that out @a_horse_with_no_name. I wrote 'so' instead of 'and' by mistake. – Rachana Gandhi Feb 14 '20 at 07:38
  • 1
    Why would it be meaningfully bigger? It would have some dupications compared to currently. But that kind of design is what relational DBMSs are for. Use the right design. PS Your current design with an ordered index & the link above does not necessarily create a large intermediate table. Also you can cross apply or otherwise use tables from a function returning a table from one row. Also learn about relational division in SQL. PS Your current description of what you want is very unclear. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. – philipxy Feb 14 '20 at 07:49
  • Thanks for the help @philipxy , i'll check it out and keep that in mind. – Rachana Gandhi Feb 14 '20 at 08:16
  • 1
    This design is wrong, as you were told already, but there is an approach. I'll post a solution as soon as this gets reopened... And please add the SQL-Server's version to the tags... – Shnugo Feb 14 '20 at 09:38
  • Are the values always unique, i.e. you cannot have `'F,O,O'`? – HABO Feb 14 '20 at 16:00
  • @HABO yes the values are unique for each row – Rachana Gandhi Feb 17 '20 at 04:39
  • 1
    You could use [`String_Split`](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15) to split the evil column into separate values and then [`String_Agg`](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15) with an `order by` clause to reassemble them in alphabetical order. At that point they are easy to compare. – HABO Feb 17 '20 at 04:46
  • I actually found a solution @HABO , But i don't know how to post it because the question is still closed. But thanks for the help everyone! – Rachana Gandhi Feb 17 '20 at 05:05
  • @RachanaGandhi As it stands this question won't get reopened again. You might place a new question (try to avoid your initial erros :-) ) and place a link here. Place your own attempt either as *your own attempt* in your question or add it as a self-answer. – Shnugo Feb 17 '20 at 08:20

2 Answers2

2

Just explicitly search for the values

SELECT *
FROM Table
WHERE Column IN ('X,Y', 'Y,X')

If you have leading or lagging spaces you can substitute spaces for a blank string to ensure exact matches can be found. Also, if your column varies from upper to lower case you can use UPPER to convert. Combining both of these should ensure you get a match

urdearboy
  • 14,439
  • 5
  • 28
  • 58
  • But this was just an example. My real column contains values greater than 20 which are separated by commas. I cant check every possible combination manually – Rachana Gandhi Feb 14 '20 at 06:23
  • 1
    Hi @RachanaGandhi then give a better example...For me this is vote up. – VBoka Feb 14 '20 at 06:24
  • So your example is not representative of your actual problem, which means you need to given the data you gave us. *Help us help you*. Voting to close as the question does not address the actual problem – urdearboy Feb 14 '20 at 06:24
0

you can use a like('%X%') clause twice to filter for this.

your condition should be in this format;

 {col} like ('%X%') and {col} like('%Y%')
LJ01
  • 589
  • 4
  • 11
  • 2
    This would pull out column 4 as well which OP does not want – urdearboy Feb 14 '20 at 06:18
  • 1
    @undearboy not entirely sure quite how I managed that - I updated my answer to what I intended to suggest – LJ01 Feb 14 '20 at 06:50
  • 1
    This will not give anything. It will return 'X,Y' also 'Y,X' yes but also it would return 'X,Y,Z' or any number of letters in combination with X and Y. This is not good for first example or for the second. – VBoka Feb 14 '20 at 06:53