Following are two colmns in a table, I want to get only rows
where column type value is in group_type column

Example

TYPE     GROUP_TYPE

V        V,RX
A        V,RX,RD,F
RX        V,RX,RM,RS,RW,RE
RW        RX,RM,RX,RW
X        V,RX,RM,RS,A

for above set of rows, my select statement should retrun following rows

TYPE     GROUP_TYPE

V        V,RX
RX        V,RX,RM,RS,RW,RE
RW        RX,RM,RX,RW

How to write SELECT Statement for above scenerio
Any help please,

thanks
kalyan

This should work:

SELECT TYPE, GROUP_TYPE
  FROM YourTable
  WHERE CHARINDEX(',' + TYPE + ',', ',' + GROUP_TYPE + ',') > 0;

HTH
Vern Rabe

Show quoteHide quote

Imagine how much simpler it would all be if your table was normalized.
I bet you wouldn't even have to ask anyone how to do it then.

I assumed that TYPE did not have any leading blanks, though the sample
data provided seemed to show some.  I also assumed that TYPE would be
fixed length, thus requiring the RTRIM().

CREATE TABLE Junk
(TYPE char(3), GROUP_TYPE varchar(30))

INSERT Junk VALUES ('V', 'V,RX')
INSERT Junk VALUES ('A', 'V,RX,RD,F')
INSERT Junk VALUES ('RX','V,RX,RM,RS,RW,RE')
INSERT Junk VALUES ('RW','RX,RM,RX,RW')
INSERT Junk VALUES ('X', 'V,RX,RM,RS,A')

SELECT *
  FROM Junk
WHERE ',' + GROUP_TYPE + ',' LIKE '%,' + RTRIM(TYPE) + ',%'

TYPE GROUP_TYPE                    
---- ------------------------------
V    V,RX
RX   V,RX,RM,RS,RW,RE
RW   RX,RM,RX,RW

Roy Harvey
Beacon Falls, CT

On Thu, 27 Jul 2006 08:33:02 -0700, Kalyan
<Kal***@discussions.microsoft.com> wrote:

Show quoteHide quote

The best approach is to consider redesigning your tables to have one value
per column. With such CSV lists crammed into tables, you'll end up writing
so much convoluted code, not to mention about the difficulty in implementing
constraints and formulating simpler query expressions.

Trying to match lowercase
too long...
datetime between problem
SQL Join question - getting most recent value in a history table
Select problem Msg. 156