בתוך בסיס הנתונים שלנו קיימות מחרוזות שמכילות מספר נתונים כאשר הנתונים מופרדים בתוך הטקסט עם פסיקים.
באתגר החודש אתם צריכים לכתוב שאילתא שמפרקת את המחרוזות האלה לרשומות נפרדות, ראו דוגמא למטה.
מעבר לכך, ניתן להשתמש בכל יכולת של בסיס הנתונים.
ניתן ליישם את האתגר בסביבת SQL Server או Oracle, לבחירתכם.
דוגמא:
SQL> desc list Name Null? Type ----------------------------------------- -------- ---------------------------- COUNTRIES VARCHAR2(100) SQL> select * from list; COUNTRIES ------------------------------------------------------- Israel,England,France 1 row selected. SQL> @split_list.sql COUNTRY ------------------------------------------------- Israel France England 3 rows selected.
אמנם בדוגמא הרצנו את השליפה שלנו מתוך סקריפט, אך זה רק בשביל לא להראות את השליפה מתחתיה.
מספר הנחות:
- אין אנו יודעים את כמות השורות (ערכים מופרדים עם פסיקים) האפשרית – כלומר, אין להסתמך על מספר קבוע של שורות.
- כאמור לעיל, אין להשתמש ב-User defined functions, אלא אך ורק ביכולות בסיס הנתונים עצמו.
- יש יותר מדרך אחת לעשות כן, בשני בסיסי הנתונים.
- האתגר הוא לעשות זאת בשליפה אחת (היא יכולה להיות מסובכת יותר או פחות) בלבד – לא במספר שלבים.
כדי להשתתף באתגר יש להירשם לאתר ולשלוח את הפתרונות לildba@brillix.co.il
בין הפותרים נכונה תוגרל הזמנה זוגית לסרט
שיהיה בהצלחה!
צוות אתר ilDBA.
—————————————————————————————————————————————————
עידכון: 07.04.11
חבר הקהילה הזוכה של אתגר חודש מרץ 2011 הוא: אלכס פרידמן!
אימייל יישלח לזוכה.
פיתרון האתגר של חודש זה:
כמו שציינו באתגר עצמו, ישנן דרכים רבות לענות על התנאים שפירטנו לעיל. הזוכה הוגרל מבין כל התשובות הנכונות שנשלחו על ידי חברי הקהילה.
בהמשך הרשומה נציג בפניכם את מקצת התשובות הנכונות שנשלחו. לא התייחסנו ליעילות השאילתה.
תשובות בסביבת Oracle:
WITH t AS ( SELECT countries or_str from list ) SELECT SUBSTR( or_str, INSTR(',' || or_str,',',1,LEVEL), INSTR(or_str || ',',',',1,LEVEL) - INSTR(',' || or_str,',',1,LEVEL)) SubString FROM t CONNECT BY LEVEL <= LENGTH(or_str)-LENGTH(REPLACE(or_str,',')) + 1 AND PRIOR or_str = or_str AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL --------------------------------------------------------------------------------------------------------- select distinct ltrim(regexp_substr(countries ,'[^,]+{1}',1,level)) COUNTRY from list connect by level <= length(regexp_replace(countries ,'[^,]*'))+ 1 --------------------------------------------------------------------------------------------------------- SELECT REGEXP_SUBSTR(countries, '[^,]+', 1, LEVEL) FROM (SELECT ROWNUM AS id, countries FROM list) CONNECT BY INSTR(countries, ',', 1, LEVEL-1) > 0 AND id = PRIOR id AND PRIOR DBMS_RANDOM.normal IS NOT NULL; --------------------------------------------------------------------------------------------------------- with list_one_row as (select RTRIM(XMLAGG(XMLELEMENT(e,countries || ',')).EXTRACT('//text()'),',') countries from list) select substr(countries,n+1,m-n-1) split from ( select countries, case when rn=1 then 0 else instr(countries,',',1,rn-1) end n, case when instr(countries,',',1,rn)=0 then length(countries)+1 else instr(countries,',',1,rn) end m from list_one_row, (select level rn from dual connect by level <= (select length(countries)-length(replace(countries,','))+1 from list_one_row)) ); --------------------------------------------------------------------------------------------------------- select substr(a, decode(rownum,1,1,instr(a,',',1,rownum-1)+1), decode(instr(a,',',1,rownum),0,length(a),instr(a,',',1,rownum)-decode(rownum,1,1,instr(a,',',1,rownum-1)+1))) as list from (select 'a,ab,abba,c,cde,123,346,678' as a from dual) connect by rownum <= (length(a) - length(replace(a,',',null))) + 1;
תשובות בסביבת SQL SERVER:
DECLARE @List VARCHAR(MAX) = 'Israel,England,France,United States'; WITH splitter AS ( SELECT 1 AS ItemNumber, SUBSTRING(@List, 0, CHARINDEX(',', @List)) AS Item, SUBSTRING(@List, CHARINDEX(',', @List)+1, LEN(@List) - CHARINDEX(',', @List)) AS Rest UNION ALL SELECT ItemNumber + 1, SUBSTRING(Rest, 0, CHARINDEX(',', Rest)), SUBSTRING(Rest, CHARINDEX(',', Rest) + 1, LEN(Rest)-CHARINDEX(',', Rest)) FROM splitter WHERE ItemNumber <= LEN(@List) - LEN(REPLACE(@List,',','')) ) SELECT CASE WHEN ItemNumber > LEN(@List) - LEN(REPLACE(@List,',','')) THEN LTRIM(RTRIM(Rest)) ELSE LTRIM(RTRIM(Item)) END Country FROM splitter --------------------------------------------------------------------------------------------------------- --For 2008 version Declare @S VarChar(Max)='Israel,England,France'; With T As (Select Left(@S,T1.I-1) S1, Right(@S,Len(@S)-T1.I)+Case When @S Like '%,' Then '' Else ',' End S From (Select CHARINDEX(',',@S) I) T1 Union All Select Left(T1.S,T1.I-1) S1, Right(T1.S,Len(T1.S)-T1.I) S From (Select S, CHARINDEX(',',S) I From T) T1 Where Len(T1.S)>0) Select S1 From T Option (MaxRecursion 0); --For 2005 version Declare @S VarChar(Max)='Israel,England,France'; Set @S='Select '''+REPLACE(@S,',',''' S Union All Select ''')+''' S;'; Exec(@S);
תודה לכל משתתפי האתגר,
ביום ראשון נפרסם את אתגר אפריל. יש למה לחכות!
צוות אתר ilDBA