请教一个问题:如何从一个列中提取相关的关键字,将它更新到另一列上
有两个表:表1(客户姓名,客户地址),表2(编号,城市)我想在表1再添加一列(城市),把客户地址中的城市名字提取出来更新到另一列,但是客户地址没有规律,比如,有的是吉林省长春市**,有的是北京海淀区,我只要长春、北京就可以,把它们更新到同一条记录的另一列,因为没有规律,所以我自己做了一个表2,把城市名字先列出来,想用like来写,但是怎么写都不行,请问各位大大,能不能帮个忙啊?
----------Function Name: fun_setCity---------- IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fun_setCity]') AND type in (N'TF','FN')) DROP FUNCTION [dbo].[fun_setCity] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Create function [dbo].[fun_setCity] (@address varchar(50)) Returns varchar(50) AS begin declare @city varchar(50) declare @provinceLength int set @provinceLength=0 declare @localLength int set @localLength=0 set @localLength=charindex('市',@address) if @localLength>0 begin set @provinceLength=charindex('省',@address) if @provinceLength<=0 begin set @provinceLength=charindex('自治区',@address) if @provinceLength>0 set @provinceLength=@provinceLength+2 else set @provinceLength=0 end if @provinceLength>0 set @city=substring(@address,@provinceLength+1,@localLength-@provinceLength-1) else begin if charindex('北京',@address)>0 set @city='北京' if charindex('上海',@address)>0 set @city='上海' if charindex('天津',@address)>0 set @city='天津' if charindex('重庆',@address)>0 set @city='重庆' end end else begin if charindex('北京',@address)>0 set @city='北京' if charindex('上海',@address)>0 set @city='上海' if charindex('天津',@address)>0 set @city='天津' if charindex('重庆',@address)>0 set @city='重庆' end return @city end Go --------创建表 Table Name: 表1---------- IF EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(N'[dbo].[表1]') AND type in (N'U')) DROP TABLE [dbo].[表1] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[表1]( [客户姓名] [varchar](50) COLLATE Chinese_PRC_CI_AS not NULL, [客户地址] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, ) ON [PRIMARY] GO ----------添加数据 Table Name: 表1---------- delete from [表1] GO insert into [表1]([客户姓名],[客户地址]) values('甲','吉林省长春市**') GO insert into [表1]([客户姓名],[客户地址]) values('乙','河南省郑州市**') GO insert into [表1]([客户姓名],[客户地址]) values('丙','陕西省西安市**') GO insert into [表1]([客户姓名],[客户地址]) values('丁','宁夏回族自治区银川市**') GO insert into [表1]([客户姓名],[客户地址]) values('戊','新疆维吾尔自治区克拉玛依市**') GO insert into [表1]([客户姓名],[客户地址]) values('己1','北京市海淀区**') GO insert into [表1]([客户姓名],[客户地址]) values('己2','北京海淀区**') GO insert into [表1]([客户姓名],[客户地址]) values('庚1','上海市浦东新区**') GO insert into [表1]([客户姓名],[客户地址]) values('庚2','上海浦东新区**') GO insert into [表1]([客户姓名],[客户地址]) values('辛1','重庆市沙坪坝区**') GO insert into [表1]([客户姓名],[客户地址]) values('辛2','重庆沙坪坝区**') GO insert into [表1]([客户姓名],[客户地址]) values('申1','天津市南开区**') GO insert into [表1]([客户姓名],[客户地址]) values('申2','天津南开区**') GO -----------------添加字段----------------------- IF EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(N'[dbo].[表1]') AND type in (N'U')) begin IF not EXISTS (SELECT * FROM syscolumns WHERE name = N'城市') begin alter table[表1] add [城市] varchar(50) end end GO -----------------显示原始表----------------------- select * from [dbo].[表1] ----------------------------------- update [dbo].[表1] set [城市]=[dbo].fun_setCity([客户地址]) ----------------更新之后的表------------------ select * from [dbo].[表1] ------------------结束------------------