none
SQL query help

    Question

  • Need help pls

    I have table 1, with column A, B ,C.
    Table 2, with G, H, I, I need your help in writing an query that would search value of G in column C and display if it matches. Value of G would be part of short string data type of C.Table 1 and 2 are related with column A and I.

    Issues is that since value of G is not exactly same as that of C, we need to search the match string og G in C.

    Your help is highly appreciated.

    Thanks.

    Monday, July 15, 2013 9:27 AM

Answers

All replies

  • Just guessing

    CREATE TABLE w
    (
     COL1 VARCHAR(20)
    )

    INSERT INTO W VALUES ('URIDIMANT')
    INSERT INTO W VALUES ('DIMANT')
    INSERT INTO W VALUES ('URIRON')
    INSERT INTO W VALUES ('RR')

    CREATE TABLE w1
    (
     COL1 VARCHAR(20)
    )

    INSERT INTO W1 VALUES ('URIDIMANT')
    INSERT INTO W1 VALUES ('DIMANTRON')
    INSERT INTO W1 VALUES ('URI')
    INSERT INTO W1 VALUES ('URIRON')


    SELECT W.COL1,W1.COL1 FROM W JOIN W1
    ON W.col1 LIKE '%' +  W1.col1 + '%'

    DROP TABLE W
    DROP TABLE W1


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, July 15, 2013 9:31 AM
    Answerer
  • Post DDL and sample data ,so we do not have to guess,

    try this (not tested one),

    select T1.A,T2.I,T1.c,T2.G from
    T1 inner join T2 
    ON T1.A=T2.I
    where T1.c like '%'+T2.G+'%'

    Monday, July 15, 2013 9:35 AM
  • Hi Alagu Selvan,

    try this 

    SELECT T1.C 
    FROM T1 
    INNER JOIN T2 ON T1.A=T2.I 
    WHERE T1.C LIKE '%'+ convert( nvarchar,T2.G ) +'%'

    Monday, July 15, 2013 11:22 AM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. Why should we answer you? How should we treat people that this rude? Want to try again and treat us so badly? 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, July 15, 2013 6:50 PM