אתגר החודש – אתגר מרץ

בתוך בסיס הנתונים שלנו קיימות מחרוזות שמכילות מספר נתונים כאשר הנתונים מופרדים בתוך הטקסט עם פסיקים.

באתגר החודש אתם צריכים לכתוב שאילתא שמפרקת את המחרוזות האלה לרשומות נפרדות, ראו דוגמא למטה.

מעבר לכך, ניתן להשתמש בכל יכולת של בסיס הנתונים.

ניתן ליישם את האתגר בסביבת 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.

אמנם בדוגמא הרצנו את השליפה שלנו מתוך סקריפט, אך זה רק בשביל לא להראות את השליפה מתחתיה.

מספר הנחות:

1. אין אנו יודעים את כמות השורות (ערכים מופרדים עם פסיקים) האפשרית  – כלומר, אין להסתמך על מספר קבוע של שורות.
2. כאמור לעיל, אין להשתמש ב-User defined functions, אלא אך ורק ביכולות בסיס הנתונים עצמו.
3. יש יותר מדרך אחת לעשות כן, בשני בסיסי הנתונים.
4. האתגר הוא לעשות זאת בשליפה אחת (היא יכולה להיות מסובכת יותר או פחות) בלבד – לא במספר שלבים.

כדי להשתתף באתגר יש להירשם לאתר ולשלוח את הפתרונות ל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;

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