我的SELECT查詢中有以下陳述句可以正確格式化在資料庫中未正確驗證的電話號碼......這很丑陋，但它有效：
COALESCE(
SUBSTRING(STUFF(STUFF(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(OFFICE_PHONE,'-',' '),',',' '),' ',''), '(', ''), ')', ''),
4,0,'-'),8,0,'-'), 1, 12),
SUBSTRING(STUFF(STUFF(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(MOBILE_PHONE,'-',' '),',',' '),' ',''), '(', ''), ')', ''),
4,0,'-'),8,0,'-'), 1, 12),
SUBSTRING(STUFF(STUFF(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(FIELD_PHONE,'-',' '),',',' '),' ',''), '(', ''), ')', ''),
4,0,'-'),8,0,'-'), 1, 12)) AS ValidPhoneNumber,
此查詢洗掉任何“壞”字符并確保數字看起來像：123-456-7890
但是，有一些條目少于 12 個字符；像123-983-12....
如何用 替換長度小于 12 個字符的條目NULL？
謝謝！
uj5u.com熱心網友回復：
我擔心你將不得不重復這樣的丑陋表達：
CASE WHEN LEN(ugly_expression) >= 12 THEN ugly_expression END
除非您為電話號碼清理程式創建存盤函式（使用CREATE FUNCTION (Transact-SQL) ）。
COALESCE(
CASE WHEN LEN( dbo.SanitizePhone(OFFICE_PHONE) ) >= 12
THEN dbo.SanitizePhone(OFFICE_PHONE) END,
CASE WHEN LEN( dbo.SanitizePhone(MOBILE_PHONE) ) >= 12
THEN dbo.SanitizePhone(MOBILE_PHONE) END,
CASE WHEN LEN( dbo.SanitizePhone(FIELD_PHONE) ) >= 12
THEN dbo.SanitizePhone(FIELD_PHONE) END
) AS ValidPhoneNumber
請注意，如果 CASE 運算式中沒有 ELSE 大小寫且 THEN 大小寫不適用，則會自動回傳 NULL。
請參閱：SQL CASE 運算式。
您還可以在子選擇中獲取經過消毒的電話號碼
SELECT
COALESCE(
CASE WHEN LEN(phone1) >= 12 THEN phone1 END,
CASE WHEN LEN(phone2) >= 12 THEN phone2 END,
CASE WHEN LEN(phone3) >= 12 THEN phone3 END
) AS ValidPhoneNumber,
...
FROM (
SELECT
STUFF(STUFF(REPLACE(...OFFICE_PHONE...))) AS phone1,
STUFF(STUFF(REPLACE(...MOBILE_PHONE...))) AS phone2,
STUFF(STUFF(REPLACE(...FIELD_PHONE...))) AS phone3,
...
FROM ...
) phones
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/524186.html