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


Searching Columns for Seperate Strings


Searching Columns for Seperate Strings

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

Group: General Forum Members
Points: 551 Visits: 950
Hi SQL ServerCentral Team,

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)

------------
am using below query...
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


it is not working for record_id 105, 106
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23583 Visits: 9730
Do it as two queries.

select *
from MyTable
where 'AA' in (Col1, Col2, Col3, Col4, Col5)
union all
select *
from MyTable
where IsNull(Col1, Col2) = IsNull(Col2, Col1)
and IsNull(Col2, Col3) = IsNull(Col3, Col2)
and IsNull(Col3, Col4) = IsNull(Col4, Col3)
and IsNull(Col4, Col5) = IsNull(Col5, Col4);



Something like that. It might suffer performance-wise, but you'll have to test to see if it's good enough for what you need.

If it's too slow, I'd pivot the columns and do your max()=min() compare that way.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Ray M
Ray M
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2167 Visits: 1076
I was thinking this could work
;WITH MySample AS (
SELECT 101 AS Record_id, 'A' AS Col1, 'A' AS Col2, 'B' AS Col3, 'A' AS Col4, 'A' AS Col5 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) = 'AA' THEN 'AA' 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



Which will return 105, but not 106
Wierd that 'AA' is not larger than 'D' The problem here is your using min/max functions on Strings which does not work for this situation.
If you have the ability to influence the design now you should consider changing to Numeric values in these tables.

Consider this simple test, which returns B as the max for Test 2 and not the significantly longer string of A's
From BOL
For character columns, MAX finds the highest value in the collating sequence.

sqlSELECT Test, MIN(Val) AS MinVal, MAX(Val) AS MaxVal
FROM (SELECT 1 AS Test, 'A' AS Val UNION ALL
SELECT 1 AS Test, 'B' AS Val UNION ALL
SELECT 1 AS Test, 'C' AS Val UNION ALL
SELECT 2 AS Test, 'A' AS Val UNION ALL
SELECT 2 AS Test, 'AAAAAAAAAA' AS Val UNION ALL
SELECT 2 AS Test, 'B' AS Val) t
GROUP BY Test


GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23583 Visits: 9730
The reason "AA" is not "larger" than "D" is because it works simply by ASCII sorting. D comes after AA alphabetically, so D > AA. That's why I suggest two queries.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5004 Visits: 5478
Duplicate post with "changed" requirements:

http://www.sqlservercentral.com/Forums/Topic1374194-391-1.aspx

_____________________________________________
"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
ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2052 Visits: 10370
Eugene Elutin (10/18/2012)
Duplicate post with "changed" requirements:

http://www.sqlservercentral.com/Forums/Topic1374194-391-1.aspx


I reckon the OP is having some difficulty simplifying the real problem. Here's a solution to the "new" requirements;
;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, ISNULL(OldCol, 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
CROSS APPLY (SELECT Oldcol = CASE WHEN 'AA' IN (Col1, Col2, Col3, Col4, Col5) THEN 'AA' ELSE NULL END) y
WHERE Newcol IS NOT NULL
OR Oldcol IS NOT NULL




Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
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