Re-entered : Searching Columns for Seperate Strings

  • Hi Team,

    Small change in my requirement.

    having 5 columns with 5 columns expiry date, each column will have a expiry date,

    and there is a final column called summary, which includes

    col1 : expiry date

    col2 : expiry date

    col3 : expiry date

    col4 : expiry date

    col5 : expiry date

    *** Searching for string should be happen in Summary column, not in all columns

    Please help

  • More than happy to help if you follow this:

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Hi Eugene,

    I agree with what ur saying,

    here am giving some more details,

    --

    ;WITH Mysample (Record_id, Col1, Col2, Col3, Col4, Col5)

    AS

    (SELECT 101, 'A', 'A', 'B', 'A', 'A' UNION ALL

    SELECT 102, 'B', 'B', NULL, NULL, 'B' UNION ALL

    SELECT 103, 'C', 'C', 'C', NULL, 'C' UNION ALL

    SELECT 104, 'D', 'C', 'D', 'D','D' UNION ALL

    SELECT 105,'AA','A','A','A','A'UNION ALL

    SELECT 106,'B','C','A','AA','D')

    SELECT Record_id, Newcol

    FROM Mysample

    CROSS APPLY (

    SELECT Newcol = CASE WHEN MAX(Newcol) = MIN(Newcol) THEN MAX(Newcol) ELSE NULL END

    FROM (VALUES (Col1), (Col2), (Col3), (Col4), (Col5)) X (Newcol)

    ) d

    WHERE Newcol IS NOT NULL

    above query i got from SSC team - Really thanks.

    now my requirement is i've to search in final column only, final column is the concatenation of 10 columns

    (col1 + col1_expiry_date + Col1 + col2_expiry_date......+col5_expiry_date)

    finally i want the search should be happen from the final_column

    Please help...

  • Sorry mate, your sample is very obscure to me and I don't have my Crystal Ball with me today to see what you can see on your site.

    Your sample doesn't correspond to what you posted in your first request. I cannot see what really problem you have, If I try to translate of what you have said I got the following:

    -- that what I can see as your table:

    declare @tbl table

    (

    Col1 VARCHAR(10), ExpDate1 DATETIME,

    Col2 VARCHAR(10), ExpDate2 DATETIME,

    Col3 VARCHAR(10), ExpDate3 DATETIME,

    Col4 VARCHAR(10), ExpDate4 DATETIME,

    Col5 VARCHAR(10), ExpDate5 DATETIME)

    -- If you want to search for string in "CONCATENATED" value of above columns you can do the following:

    ;with cte

    as

    (

    select *

    ,Col1+' '+CONVERT(varchar(30),ExpDate1, [whateverformatyouwant]) + ' '

    +Col2+' '+CONVERT(varchar(30),ExpDate2, [whateverformatyouwant]) + ' '

    +Col3+' '+CONVERT(varchar(30),ExpDate3, [whateverformatyouwant]) + ' '

    +Col4+' '+CONVERT(varchar(30),ExpDate4, [whateverformatyouwant]) + ' '

    +Col5+' '+CONVERT(varchar(30),ExpDate5, [whateverformatyouwant]) AS ConcatenatedCol

    from @tbl

    )

    select * from cte where ConcatenatedCol LIKE '%search word%'

    Something tells me, that the above is not what you are looking for. But again, your explanations too obscure (at least to me).

    Now, if for any reason the sample I gave is exactly what you trying to do, than I can give you another advise: If your table is significantly large and you want such search to be acceptably quick, you better use Free-text index search feature.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Hi,

    am trying to give some more brief..

    my previous requirement is

    I having a table with 5 columns

    Record_idCol1Col2Col3Col4Col5

    ------------------------------------------------------

    101AABAA

    102BBNULLNULLB

    103CCCNULLC

    104DCDDD

    105AAAAAA

    106BCAAAD

    if record 101 contains all 5 columns same (i.e:A) then only "A" should be inserted into another

    table

    in record 101, col3 contains "B" then it should be skipped.

    in record 102 - 3 columns contains B and 2 columns contain NULL, then it should be inserted.

    Record 101 - SKIP

    Record 102 - Insert B in to another table.

    Record 103 - Insert C in to another table.

    Record 104 - SKIP

    * if in any record that contains "AA" in any column, it should return "AA" (records 105, 106)

    Now i've inserted a new column called summary (summary column is a concatenation of all columns

    *** i want the same above logic should be implemented in summary column.

  • ;WITH Mysample (Record_id, Col1, Col2, Col3, Col4, Col5)

    AS

    (SELECT 101, 'A', 'A', 'B', 'A', 'A' UNION ALL

    SELECT 102, 'B', 'B', NULL, NULL, 'B' UNION ALL

    SELECT 103, 'C', 'C', 'C', NULL, 'C' UNION ALL

    SELECT 104, 'D', 'C', 'D', 'D','D' UNION ALL

    SELECT 105,'AA','A','A','A','A'UNION ALL

    SELECT 106,'B','C','A','AA','D')

    , CT

    AS

    (

    SELECT DISTINCT Record_id, Newcol

    FROM Mysample

    CROSS APPLY (SELECT Newcol FROM (VALUES (Col1), (Col2), (Col3), (Col4), (Col5)) X (Newcol) ) n

    WHERE Newcol IS NOT NULL

    )

    SELECT Record_id, MAX(Newcol) AS Newcol

    FROM CT

    GROUP BY Record_id

    HAVING COUNT(*) = 1

    UNION

    SELECT Record_id, Newcol

    FROM CT WHERE Newcol = 'AA'

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply