如何在SQL 使用正则表达式

Question

--> 测试时间:2009-07-10 00:06:05
--> 我的淘宝: http://shop36766744.taobao.com/if object_id('[tab]') is not null drop table [tab]
create table [tab]([value] varchar(12))
insert [tab]
select '513-241-9517' union all
select '7344289988'
select '('+left(replace(value,'-',''),3)+')'+substring(replace(value,'-',''),3,3)+'-'+right(replace(value,'-',''),4) from tab
/*
                        
----------------------- 
[phone]
[phone]（所影响的行数为 2 行）*/

Answer 1

--> 测试时间:2009-07-10 00:06:05
--> 我的淘宝: http://shop36766744.taobao.com/if object_id('[tab]') is not null drop table [tab]
create table [tab]([value] varchar(40))
insert [tab]
select '513-241-9517' union all
select '7344289988' union all
select '[phone]'
select case when charindex('(',value)>0 then value else
'('+left(replace(value,'-',''),3)+') '+substring(replace(value,'-',''),3,3)+'-'+right(replace(value,'-',''),4)  end from tab/*
                                         
---------------------------------------- 
[phone]
[phone]
[phone]（所影响的行数为 3 行）*/

Answer 2

-- =========================================
-- -----------t_mac 小编-------------
   ---希望有天成为大虾---- 
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
  DROP TABLE tb
GO
CREATE TABLE tb(col varchar(20))
go
insert into tb
select '513-241-9517' union all
select '7344289988' union all
select '[phone]'
go
select case when CHARINDEX(')',col)>0 then COL 
else   '('+left(replace(col,'-',''),3)+') '+SUBSTRING(replace(col,'-',''),4,3)+'-'+right(COL,4) end as col from tb
/*------------
(513)  241-9517
(734)  428-9988
[phone]
-------*/

Answer 3

---------------------------------
--  Author: htl258(Tony)
--  Date  : 2009-07-10 08:23:30
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([member_id] int,[home_phone] nvarchar(14),[HOME_CELL_PHONE] nvarchar(14),[home_fax] nvarchar(12),[busi_phone] nvarchar(12),[busi_fax] nvarchar(12))
Insert tb
Select 1,'[phone]','7347183755','7349309005','7349300009','7349309005' union all
Select 21,null,'7344289988',null,null,'7344289988' union all
Select 24,null,'7349303832',null,null,'7346682448' union all
Select 32,'[phone]','225-963-1499','225-272-3335',null,null union all
Select 33,'3056950170','7342764001',null,'3056950170','2397903700' union all
Select 34,'[phone]','661-822-8887','661-825-8227',null,null union all
Select 36,'908-647-8485','908-209-9094','','908-350-3112','313-557-4106' union all
Select 49,null,'[phone]',null,null,null union all
Select 50,null,null,null,null,null union all
Select 51,'513-574-5854','513-284-4187','513-241-9517',null,null
Go
--Select * from tb-->SQL查询如下:
If not object_id('[getstr]') is null
Drop function [getstr]
Go
create function getstr(@s nvarchar(20))
returns nvarchar(20)
as
begin
    set @s=replace(replace(replace(replace(@s,'(',''),')',''),'-',''),' ','')
    return(select '('+left(@s,3)+')'+substring(@s,4,3)+'-'+right(@s,4))
end
goselect [member_id],
    dbo.[getstr]([home_phone]) [home_phone] ,
    dbo.[getstr]([HOME_CELL_PHONE]) [HOME_CELL_PHONE],
    dbo.[getstr]([home_fax]) [home_fax],
    dbo.[getstr]([busi_phone]) [busi_phone],
    dbo.[getstr]([busi_fax]) [busi_fax]
from tb/*
member_id   home_phone           HOME_CELL_PHONE      home_fax             busi_phone           busi_fax
----------- -------------------- -------------------- -------------------- -------------------- --------------------
1           [phone]        [phone]        [phone]        [phone]        [phone]
21          NULL                 [phone]        NULL                 NULL                 [phone]
24          NULL                 [phone]        NULL                 NULL                 [phone]
32          [phone]        [phone]        [phone]        NULL                 NULL
33          [phone]        [phone]        NULL                 [phone]        [phone]
34          [phone]        [phone]        [phone]        NULL                 NULL
36          [phone]        [phone]        ()-                  [phone]        [phone]
49          NULL                 [phone]        NULL                 NULL                 NULL
50          NULL                 NULL                 NULL                 NULL                 NULL
51          [phone]        [phone]        [phone]        NULL                 NULL(10 行受影响)
*/

Answer 4

---------------------------------
--  Author: htl258(Tony)
--  Date  : 2009-07-10 08:23:30
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([member_id] int,[home_phone] nvarchar(14),[HOME_CELL_PHONE] nvarchar(14),[home_fax] nvarchar(12),[busi_phone] nvarchar(12),[busi_fax] nvarchar(12))
Insert tb
Select 1,'[phone]','7347183755','7349309005','7349300009','7349309005' union all
Select 21,'','7344289988',null,null,'7344289988' union all
Select 24,null,'7349303832',null,null,'7346682448' union all
Select 32,'[phone]','225-963-1499','225-272-3335',null,null union all
Select 33,'3056950170','7342764001',null,'3056950170','2397903700' union all
Select 34,'[phone]','661-822-8887','661-825-8227',null,null union all
Select 36,'908-647-8485','908-209-9094','','908-350-3112','313-557-4106' union all
Select 49,null,'[phone]',null,null,null union all
Select 50,null,null,null,null,null union all
Select 51,'513-574-5854','513-284-4187','513-241-9517',null,null
Go
--Select * from tb-->SQL查询如下:
If not object_id('[getstr]') is null
Drop function [getstr]
Go
create function getstr(@s nvarchar(20))
returns nvarchar(20)
as
begin
    if @s='' set @s=null else set @s=replace(replace(replace(replace(@s,'(',''),')',''),'-',''),' ','')
    return(select '('+left(@s,3)+')'+substring(@s,4,3)+'-'+right(@s,4))
end
goselect [member_id],
    dbo.[getstr]([home_phone]) [home_phone] ,
    dbo.[getstr]([HOME_CELL_PHONE]) [HOME_CELL_PHONE],
    dbo.[getstr]([home_fax]) [home_fax],
    dbo.[getstr]([busi_phone]) [busi_phone],
    dbo.[getstr]([busi_fax]) [busi_fax]
from tb/*
member_id   home_phone           HOME_CELL_PHONE      home_fax             busi_phone           busi_fax
----------- -------------------- -------------------- -------------------- -------------------- --------------------
1           [phone]        [phone]        [phone]        [phone]        [phone]
21          NULL                 [phone]        NULL                 NULL                 [phone]
24          NULL                 [phone]        NULL                 NULL                 [phone]
32          [phone]        [phone]        [phone]        NULL                 NULL
33          [phone]        [phone]        NULL                 [phone]        [phone]
34          [phone]        [phone]        [phone]        NULL                 NULL
36          [phone]        [phone]        NULL                 [phone]        [phone]
49          NULL                 [phone]        NULL                 NULL                 NULL
50          NULL                 NULL                 NULL                 NULL                 NULL
51          [phone]        [phone]        [phone]        NULL                 NULL(10 行受影响)
*/再改下,考虑空字节字符串.