27,579
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[id] INT IDENTITY(1,1) PRIMARY KEY,
[info] NVARCHAR(MAX)
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'龙沙区卜奎南大街(交通管理处对过) 0452-2228858')
INSERT INTO dbo.[t] VALUES(N'曲线小区6门市地下3,4号 18746016893')
INSERT INTO dbo.[t] VALUES(N'南岗区曲线街76号 86420040')
INSERT INTO dbo.[t] VALUES(N'南岗区理治街29号208 209 0451-82737507')
INSERT INTO dbo.[t] VALUES(N'南岗区淮河路365-8号 0451-82384075')
INSERT INTO dbo.[t] VALUES(N'香山路12-2号金源小区E栋1-5号层3号门市0451-55529958')
INSERT INTO dbo.[t] VALUES(N'望奎县五街二十九委29幢289号0455-6711616')
INSERT INTO dbo.[t] VALUES(N'软件园小区A-2栋8号5层501、502、503室0451-55652189')
INSERT INTO dbo.[t] VALUES(N'北大营圣源小区1号楼4,5号门市 0467-5066001')
INSERT INTO dbo.[t] VALUES(N'立新街三委(繁华大街325号)0455-4622932')
INSERT INTO dbo.[t] VALUES(N'中央大街95号 0451---56768888')
INSERT INTO dbo.[t] VALUES(N'卫生路西侧兴华建材大市场3号楼A1B1-A5B5 0455-6499999')
INSERT INTO dbo.[t] VALUES(N'0455-7332280')
INSERT INTO dbo.[t] VALUES(N'海伦市向阳大街路东光华路北')
INSERT INTO dbo.[t] VALUES(NULL)
GO
----------------- 以上为测试表及测试数据 -----------------------------
----------------- 创建表值函数 ---------------------------------------
IF OBJECT_ID('dbo.Fun_SplitAddressMobile') IS NOT NULL
DROP FUNCTION dbo.Fun_SplitAddressMobile
GO
-- =============================================
-- Author: yenange
-- Create date: 2019-06-18
-- Description: 分离出地址与电话
-- =============================================
CREATE FUNCTION dbo.Fun_SplitAddressMobile
(
@info NVARCHAR(MAX)
)
RETURNS
@r TABLE
(
addr NVARCHAR(MAX),
mobile NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @mobile NVARCHAR(MAX),@tempstr NVARCHAR(MAX),@addr NVARCHAR(MAX),@old NVARCHAR(MAX)
IF @info>''
BEGIN
SET @old=@info;
SET @mobile=''
WHILE len(@info)>0
BEGIN
SET @tempstr=RIGHT(@info,1)
IF CHARINDEX(@tempstr,'0123456789-')>0
BEGIN
SET @mobile=@tempstr+@mobile;
END
ELSE
BEGIN
SET @addr=SUBSTRING(@old,1,LEN(@old)-LEN(@mobile))
BREAK;
END
SET @info=SUBSTRING(@info,1,LEN(@info)-1)
END
END
INSERT INTO @r VALUES (rtrim(ltrim(@addr)),@mobile);
RETURN
END
GO
--- 查询得到结果
SELECT *
,(SELECT addr FROM dbo.Fun_SplitAddressMobile(t.info)) AS addr
,(SELECT mobile FROM dbo.Fun_SplitAddressMobile(t.info)) AS mobile
FROM t
USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[id] INT IDENTITY(1,1) PRIMARY KEY,
[info] NVARCHAR(4000)
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'龙沙区卜奎南大街(交通管理处对过) 0452-2228858')
INSERT INTO dbo.[t] VALUES(N'曲线小区6门市地下3,4号 18746016893')
INSERT INTO dbo.[t] VALUES(N'南岗区曲线街76号 86420040')
INSERT INTO dbo.[t] VALUES(N'南岗区理治街29号208 209 0451-82737507')
INSERT INTO dbo.[t] VALUES(N'南岗区淮河路365-8号 0451-82384075')
INSERT INTO dbo.[t] VALUES(N'香山路12-2号金源小区E栋1-5号层3号门市0451-55529958')
INSERT INTO dbo.[t] VALUES(N'望奎县五街二十九委29幢289号0455-6711616')
INSERT INTO dbo.[t] VALUES(N'软件园小区A-2栋8号5层501、502、503室0451-55652189')
INSERT INTO dbo.[t] VALUES(N'北大营圣源小区1号楼4,5号门市 0467-5066001')
INSERT INTO dbo.[t] VALUES(N'立新街三委(繁华大街325号)0455-4622932')
INSERT INTO dbo.[t] VALUES(N'中央大街95号 0451---56768888')
INSERT INTO dbo.[t] VALUES(N'卫生路西侧兴华建材大市场3号楼A1B1-A5B5 0455-6499999')
INSERT INTO dbo.[t] VALUES(N'0455-7332280')
INSERT INTO dbo.[t] VALUES(N'海伦市向阳大街路东光华路北')
INSERT INTO dbo.[t] VALUES(NULL)
GO
----------------- 以上为测试表及测试数据 -----------------------------
----------------- 创建表值函数 ---------------------------------------
IF OBJECT_ID('dbo.Fun_SplitAddressMobile') IS NOT NULL
DROP FUNCTION dbo.Fun_SplitAddressMobile
GO
-- =============================================
-- Author: yenange
-- Create date: 2019-06-18
-- Description: 分离出地址与电话
-- =============================================
CREATE FUNCTION dbo.Fun_SplitAddressMobile
(
@info NVARCHAR(4000)
)
RETURNS
@r TABLE
(
addr NVARCHAR(4000),
mobile NVARCHAR(4000)
)
AS
BEGIN
DECLARE @mobile NVARCHAR(4000),@tempstr NVARCHAR(4000),@addr NVARCHAR(4000),@old NVARCHAR(4000)
IF @info>''
BEGIN
SET @old=@info;
SET @mobile=''
WHILE len(@info)>0
BEGIN
SET @tempstr=RIGHT(@info,1)
IF CHARINDEX(@tempstr,'0123456789-')>0
BEGIN
SET @mobile=@tempstr+@mobile;
END
ELSE
BEGIN
SET @addr=SUBSTRING(@old,1,LEN(@old)-LEN(@mobile))
BREAK;
END
SET @info=SUBSTRING(@info,1,LEN(@info)-1)
END
END
INSERT INTO @r VALUES (rtrim(ltrim(@addr)),@mobile);
RETURN
END
GO
--- 查询得到结果
SELECT *
,(SELECT addr FROM dbo.Fun_SplitAddressMobile(t.info)) AS addr
,(SELECT mobile FROM dbo.Fun_SplitAddressMobile(t.info)) AS mobile
FROM t
2000 没有 nvarchar(max) , 改成上面的再试下看吧。USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[id] INT IDENTITY(1,1) PRIMARY KEY,
[info] NVARCHAR(MAX)
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'龙沙区卜奎南大街(交通管理处对过) 0452-2228858')
INSERT INTO dbo.[t] VALUES(N'曲线小区6门市地下3,4号 18746016893')
INSERT INTO dbo.[t] VALUES(N'南岗区曲线街76号 86420040')
INSERT INTO dbo.[t] VALUES(N'南岗区理治街29号208 209 0451-82737507')
INSERT INTO dbo.[t] VALUES(N'南岗区淮河路365-8号 0451-82384075')
INSERT INTO dbo.[t] VALUES(N'香山路12-2号金源小区E栋1-5号层3号门市0451-55529958')
INSERT INTO dbo.[t] VALUES(N'望奎县五街二十九委29幢289号0455-6711616')
INSERT INTO dbo.[t] VALUES(N'软件园小区A-2栋8号5层501、502、503室0451-55652189')
INSERT INTO dbo.[t] VALUES(N'北大营圣源小区1号楼4,5号门市 0467-5066001')
INSERT INTO dbo.[t] VALUES(N'立新街三委(繁华大街325号)0455-4622932')
INSERT INTO dbo.[t] VALUES(N'中央大街95号 0451---56768888')
INSERT INTO dbo.[t] VALUES(N'卫生路西侧兴华建材大市场3号楼A1B1-A5B5 0455-6499999')
INSERT INTO dbo.[t] VALUES(N'0455-7332280')
INSERT INTO dbo.[t] VALUES(N'海伦市向阳大街路东光华路北')
INSERT INTO dbo.[t] VALUES(NULL)
GO
----------------- 以上为测试表及测试数据 -----------------------------
----------------- 创建表值函数 ---------------------------------------
IF OBJECT_ID('dbo.Fun_SplitAddressMobile') IS NOT NULL
DROP FUNCTION dbo.Fun_SplitAddressMobile
GO
-- =============================================
-- Author: yenange
-- Create date: 2019-06-18
-- Description: 分离出地址与电话
-- =============================================
CREATE FUNCTION dbo.Fun_SplitAddressMobile
(
@info NVARCHAR(MAX)
)
RETURNS
@r TABLE
(
addr NVARCHAR(MAX),
mobile NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @mobile NVARCHAR(MAX),@tempstr NVARCHAR(MAX),@addr NVARCHAR(MAX),@old NVARCHAR(MAX)
IF @info>''
BEGIN
SET @old=@info;
SET @mobile=''
WHILE len(@info)>0
BEGIN
SET @tempstr=RIGHT(@info,1)
IF CHARINDEX(@tempstr,'0123456789-')>0
BEGIN
SET @mobile=@tempstr+@mobile;
END
ELSE
BEGIN
SET @addr=SUBSTRING(@old,1,LEN(@old)-LEN(@mobile))
BREAK;
END
SET @info=SUBSTRING(@info,1,LEN(@info)-1)
END
END
INSERT INTO @r VALUES (rtrim(ltrim(@addr)),@mobile);
RETURN
END
GO
--- 查询得到结果
SELECT * FROM t CROSS APPLY dbo.Fun_SplitAddressMobile(t.info) AS f