Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Re-entered : Searching Columns for Seperate Strings Expand / Collapse
Author
Message
Posted Thursday, October 18, 2012 2:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:52 AM
Points: 227, Visits: 705
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


  Post Attachments 
image1.JPG (5 views, 42.07 KB)
Post #1374194
Posted Thursday, October 18, 2012 3:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1374207
Posted Thursday, October 18, 2012 3:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:52 AM
Points: 227, Visits: 705
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...
Post #1374229
Posted Thursday, October 18, 2012 3:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1374235
Posted Thursday, October 18, 2012 4:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:52 AM
Points: 227, Visits: 705
Hi,

am trying to give some more brief..

my previous requirement is

I having a table with 5 columns

Record_id Col1 Col2 Col3 Col4 Col5
------------------------------------------------------
101 A A B A A
102 B B NULL NULL B
103 C C C NULL C
104 D C D D D
105 AA A A A A
106 B C A AA D

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.
Post #1374252
Posted Thursday, October 18, 2012 4:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062

;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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1374270
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse