熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

sql server取出內容中漢字,數字,字母

2013-11-15 14:46:12  來源: SQL Server 

  取出數字

  IF OBJECT_ID(’DBOGET_NUMBER’) IS NOT NULL

  DROP FUNCTION DBOGET_NUMBER

  GO

  CREATE FUNCTION DBOGET_NUMBER(@S VARCHAR())

  RETURNS VARCHAR()

  AS

  BEGIN

  WHILE PATINDEX(’%[^]%’@S) >

  BEGIN

  set @s=stuff(@spatindex(’%[^]%’@s)’’)

  END

  RETURN @S

  END

  GO

  測試

  PRINT DBOGET_NUMBER(’呵呵ABCABC’)

  GO

  

  

  取出英文

  IF OBJECT_ID(’DBOGET_STR’) IS NOT NULL

  DROP FUNCTION DBOGET_STR

  GO

  CREATE FUNCTION DBOGET_STR(@S VARCHAR())

  RETURNS VARCHAR()

  AS

  BEGIN

  WHILE PATINDEX(’%[^az]%’@S) >

  BEGIN

  set @s=stuff(@spatindex(’%[^az]%’@s)’’)

  END

  RETURN @S

  END

  GO

  測試

  PRINT DBOGET_STR(’呵呵ABCABC’)

  GO

  

  取出中文

  IF OBJECT_ID(’DBOCHINA_STR’) IS NOT NULL

  DROP FUNCTION DBOCHINA_STR

  GO

  CREATE FUNCTION DBOCHINA_STR(@S NVARCHAR())

  RETURNS VARCHAR()

  AS

  BEGIN

  WHILE PATINDEX(’%[^吖座]%’@S) >

  SET @S = STUFF(@SPATINDEX(’%[^吖座]%’@S)N’’)

  RETURN @S

  END

  GO

  PRINT DBOCHINA_STR(’呵呵ABCABC’)

  GO

  SELECT * FROM (select ’ASDKG論壇K聯通DL’ as col)TB WHERE COL LIKE N’%[吖咗]%’


From:http://tw.wingwit.com/Article/program/SQLServer/201311/22305.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.