שתי דרכים לניקוי טקסט מתווים מיותרים

ניתקלתי בבעייה הבאה: נתונה עמודה בטבלה עם מספרי טלפון, ויש להסיר את כל התווים שאינם נומריים.

למשל- במקום (03) 2529959 אנחנו רוצים לקבל 032529959,

במקום +972-54-666-8888 אנחנו רוצים לקבל 972546668888,

וכו'.

אם המשימה הייתה הפוכה- להסיר את כל התווים הנומריים, היינו יכולים בצורה קצת מגושמת לקנן 10 פעמים את פקודת Replace וכך להחליף את כל אחד מעשרת התווים הנומריים במחרוזת ריקה; אלא שעם תווים לא נומריים זה בלתי אפשרי.

אפשר לכאורה לעבור בלולאה על כל התווים של כל אחד ממספרי הטלפון, ואם הוא נומרי (את זה קל לבדוק) להוריד אותו. פתרון כזה כרוך בכתיבת פונקציה סקלארית שתופעל על כל אחד ממספרי הטלפון, אלא שגם אם פתרון זה אינו פחות יעיל מהפתרון שאציג להלן – הוא מחייב כתיבת פונקצייה, הרשאות מתאימות, העלאה לייצור וכו'; ונעדיף פתרון שמסתפק בהרשאות Select צנועות וללא שינויים בסכימה, בעזרת CTE רקורסיבי!

ניצור טבלה זמנית (בפועל תהיה לנו טבלה קיימת ומאוכלסת בנתונים) ונכניס לתוכה נתונים לבדיקה:

DROP TABLE IF EXISTS #T;

CREATE TABLE #T(Txt VARCHAR(MAX));

INSERT INTO #T VALUES('+972 (54) 4275-675'),('Geri Reshef'),('1234567890'),(' 1 2 3 4 5 6 7 8 9 0'),('a1b2c3-&$#\/?<>.');

Select * From #T;

ולשליפה:

WITH T AS

(SELECT Txt CleanTxt,

Txt OriginalTxt

FROM #T

UNION ALL

SELECT REPLACE(CleanTxt,SUBSTRING(CleanTxt,CA.Plc,1),''),

OriginalTxt

FROM T

Cross Apply (Select PATINDEX('%[^0-9]%',CleanTxt) Plc) CA

WHERE CA.Plc>0)

SELECT CleanTxt,

OriginalTxt

FROM T

WHERE PATINDEX('%[^0-9]%',CleanTxt)=0

OPTION (MAXRECURSION 0);

הסבר: TSQL אינו תומך ב-RegEx (למי שמכיר, ואני בעוונותי לא..), אבל מתברר שהתמיכה היחסית מצומצמת בעיבוד מחרוזות – מספיקה ברוב המקרים, למשל במקרה זה. אינני זוכר שנתקלתי בבעייה טקסטואלית שלא היה לה פתרון, ולכל היותר – הפתרון ה-TSQL-י היה מעט יותר מסורבל.

במקרה זה פונקציה PatIndex (בתוך פסוקית ה-Cross Apply) מזהה את מיקום המופע הראשון של תו שאינו נומרי, ותו זה (אותו מזהים על פי מיקומו הנ"ל בעזרת SubString) מוחלף במחרוזת ריקה בעזרת Replace.

תו האחוזים % בתחילת ובסוף התיאור שב-PatIndex מציין שהתו יכול להיות בכל מקום במחרוזת (אם יש כמה – הפונקציה תמצא כאמור את הראשון, אחרת – תחזיר 0),

והביטוי בסוגריים המרובעים מציין [כל מה שאינו בין 0 ל-9]: התו ^ מציין שלילה, ו 0-9 מציין את כל התווים מ-0 ועד 9. לו היינו רוצים לנקות את כל התווים הנומריים דווקא – היינו כותבים PATINDEX('%[0-9]%',CleanTxt) כלומר- ללא תו השלילה ^. לו היינו רוצים לנקות רק תווים נבחרים (נניח !@#$%) היינו יכולים להשתמש ב-5 Replace מקוננים זה בתוך זה ולוותר על ה-CTE הרקורסיבי, או לכתוב PATINDEX('%[!@#$%]%',CleanTxt). ולבסוף, לו היינו רוצים להשאיר רק תווים אלפבתיים בלטינית, היינו כותבים PATINDEX('%[^A-z]%',CleanTxt), כלומר- להסיר כל מה שאינו בין A (גדולה) ל-z (קטנה).

ה-CTE הרקורסיבי הוא כל 10 השורות הראשונות בשליפה: מ-With T as והסוגר הפותח שאחריו, ועד לסוגר הסופי בשורה העשירית. פוסט זה לא נועד להסביר כיצד פועל CTE רקורסיבי, ורק אציין שהרקורסיה מתבטאת בכך שבתוך הסוגריים פונים לאובייקט T (From T בשורה 8) שהוא האובייקט שהוגדר מחוץ לסוגריים בשורה 1 (With T As).

ה-Hint שבשורה האחרונה Option (MaxRecursion 0) נועד לאפשר לטפל גם במחרוזות מאוד ארוכות עם למעלה מ-100 תווים שונים לניקוי (במקרה זה הוא אינו הכרחי).

בעייה אחרת היא מחיקה של תווים כפולים והשארה של אחד. הכוונה בעיקר למצב בו יש בתוך הטקסט תווי רווח מיותרים, ושבין שתי מילים מפרידים מספר תאי רווח, ואנחנו רוצים להשאיר רק אחד. לא ניתן להשתמש ב-Replace ולהחליף את תו הרווח בתו ריק, כי אז לא ישארו רווחים בכלל. גם לא ניתן להחליף שני תווי רווח בתו רווח בודד מכיוון שאם יהיו 4 תווי רווח – ישארו 2, ואז מה? נצטרך לכתוב פונקציה עם לולאה שתחליף שני רווחים באחד עד שלא ישארו רווחים כפולים.

אז מה – שוב CTE רקורסיבי? חס וחלילה, הפעם טריק אחר!

קודם כל – נתונים:

DROP TABLE IF EXISTS #T;

CREATE TABLE #T(Txt VARCHAR(MAX));

INSERT INTO #T VALUES('Geri Reshef'),('Marina Maximilian Blumin'),('Joseph Robinette Biden Jr.');

Select *,

Len(Txt)-Len(Replace(Txt,' ','')) Spaces

From #T;

בשליפה ניתן לראות כמה תווי רווח בסה"כ יש בכל מחרוזת (וזה טריק שימושי כדי לספור כמה פעמים מופיע תו כלשהו, לאו דווקא רווח, בטקסט).

הניקוי נעשה כך:

Select Txt,

Len(Txt)-Len(Replace(Txt,' ','')) SpacesBefore,

CA.Clean,

Len(CA.Clean)-Len(Replace(CA.Clean,' ','')) SpacesAfter

From #T

Cross Apply (Select Replace(Replace(Replace(Txt,' ','@#'),'#@',''),'@#',' ') Clean) CA;

הסבר: ניתן לראות שמספר הרווחים קטן מ-1,7,12 ל-1,2,3 בהתאמה (כלומר- מספר תווי הרווח צריך להיות קטן ב-1 ממספר המילים). הטריק נמצא בפסוקית ה-Cross Apply בה יש שלוש פקודות Replace מקוננות:

הפנימית מחליפה כל תו רווח בשני התווים @#. יש לבחור בצירוף שאינו מופיע באף טקסט. כך 4 תווי רווח יהפכו ל-@#@#@#@#. ה-Replace השני מחליף כל היפוך של שני התווים (#@) במחרוזת ריקה. כך נישאר עם הראשון והאחרון: @#@#@#@# => @# שזהים לצמד המקורי. ה-Replace החיצוני מחליף חזרה את צמד התווים המקורי @# בתו רווח בודד.