SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Re-entered : Searching Columns for Seperate Strings


Re-entered : Searching Columns for Seperate Strings

Author
Message
Minnu
Minnu
Mr or Mrs. 500
Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)

Group: General Forum Members
Points: 567 Visits: 950
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
Attachments
image1.JPG (8 views, 42.00 KB)
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5126 Visits: 5478
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
Minnu
Minnu
Mr or Mrs. 500
Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)

Group: General Forum Members
Points: 567 Visits: 950
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...
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5126 Visits: 5478
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
Minnu
Minnu
Mr or Mrs. 500
Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)

Group: General Forum Members
Points: 567 Visits: 950
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.
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5126 Visits: 5478


;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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search