locked
Find table column pattern in a variable RRS feed

  • Question

  • I have a variable @a.  There is a table B with column A,C.  I need to find out those values of column A where column C values are a subset of @a. 

    For example @a='123456abcd'table

    Table B

    A     C

    1      abcd

    2      xyzt

    In the above case I need to get A=1 since value C pattern belongs within @a.

    Friday, August 15, 2014 2:54 PM

Answers

  • Select A
    From B
    Where C Like '%' + @a + '%'

    Tom
    • Marked as answer by John_nn Monday, August 18, 2014 1:04 PM
    Friday, August 15, 2014 3:03 PM
  • something like this?

    create table #test1
    (A int,
     C varchar(100))
     insert into #TEST1 values (1,'abcd'),(2,'xyzt'),(3,'abcd'),(4,'abcdedgh')
    select * from #test1
    
    DECLARE @FindSubString VARCHAR(MAX)
    SET @FindSubString = 'abcd'
    select A,C 
    from(
    SELECT A,C,(LEN(c) - LEN(REPLACE(c, @FindSubString, '')))/LEN(@FindSubString) CntOccuranceChars from #test1
    )T1
    where CntOccuranceChars >0
    --Prashanth

    • Marked as answer by John_nn Monday, August 18, 2014 1:04 PM
    Friday, August 15, 2014 4:09 PM

All replies

  • Select A
    From B
    Where C Like '%' + @a + '%'

    Tom
    • Marked as answer by John_nn Monday, August 18, 2014 1:04 PM
    Friday, August 15, 2014 3:03 PM
  • can you try this?

    create table #test1
    (A int,
     C varchar(100))
     insert into #TEST1 values (1,'abcd'),(2,'xyzt'),(3,'abcd')
     
    select * from #test1
    declare @a varchar(10)='123456abcd'
    
    select * from #test1 where CHARINDEX(c,@a)>0
    --Prashanth

    Friday, August 15, 2014 3:07 PM
  • This will not work as C is a subset of @a and not the other way.
    Friday, August 15, 2014 3:08 PM
  • If I just want to find the pattern 'abcd' then this will not work , as the result will return those rows which have values like 'abcdef' etc.
    Friday, August 15, 2014 3:12 PM
  • Sorry,

    Select A
    From B
    Where @a Like '%' + C + '%'

    Tom

    Friday, August 15, 2014 3:56 PM
  • something like this?

    create table #test1
    (A int,
     C varchar(100))
     insert into #TEST1 values (1,'abcd'),(2,'xyzt'),(3,'abcd'),(4,'abcdedgh')
    select * from #test1
    
    DECLARE @FindSubString VARCHAR(MAX)
    SET @FindSubString = 'abcd'
    select A,C 
    from(
    SELECT A,C,(LEN(c) - LEN(REPLACE(c, @FindSubString, '')))/LEN(@FindSubString) CntOccuranceChars from #test1
    )T1
    where CntOccuranceChars >0
    --Prashanth

    • Marked as answer by John_nn Monday, August 18, 2014 1:04 PM
    Friday, August 15, 2014 4:09 PM
  • Hi John_nn,

    I’m confused about what’s the desired result you want. Are you want to find out all matched values of column A where column C values are a subset of @a, or find out all matched values of column A where @a is a subset of column C values? If in the first scenario, you can refer to the first reply of Prashanth and the second reply of Tom. If in the second scenario, you can refer to the first reply of Tom and the second reply of Prashanth.

    If there are any misunderstanding, please elaborate the issue for further investigation.

    Regards,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Monday, August 18, 2014 10:10 AM
  • I apologize for the confusion. I used Tom's solution with an extra logic to check the length of the string to resolve it. Thanks!
    Monday, August 18, 2014 1:03 PM
  • Thanks Prashant!
    Monday, August 18, 2014 1:04 PM
  • Thanks Tom!
    Monday, August 18, 2014 1:04 PM