sql的查询条件

SQL code--sql server 2000使用临时表解决
create table tb(id int , [values] varchar(50))
insert into tb values(1,'22/11/12/13/45/123/456 ')
insert into tb values(2,'22/11/12/13/45/123/456/AA/BB/CC/DD')
insert into tb values(3,'AA/BB/CC/DD')
go
--建立一临时表tmp1.
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO tmp FROM syscolumns a, syscolumns b
--设置变量，并将变量放入另一临时表tmp2。
declare @sql as varchar(1000)
declare @str as varchar(100)
set @str = '22/11/33/44/345/123'
set @sql= 'select '+replace(@str,'/',' as id union all select ')
exec('select * into tmp2 from (' + @sql + ') t')

select m.* from tb m where id in
(
  select distinct id from
  (
    SELECT A.id, [values] = SUBSTRING(A.[values], B.id, CHARINDEX('/', A.[values] + '/', B.id) - B.id)
    FROM tb A, tmp B
    WHERE SUBSTRING('/' + A.[values], B.id, 1) = '/'
  ) t1
  where [values] in (select cast(id as varchar) from tmp2)
)

drop table tb,tmp,tmp2

/*
id          values                                            
----------- --------------------------------------------------
1           22/11/12/13/45/123/456
2           22/11/12/13/45/123/456/AA/BB/CC/DD

（所影响的行数为 2 行）
*/

 

SQL codecreate table tb(s varchar(20))
insert into tb select '101,102,103,107'
insert into tb select '104,105,106'
insert into tb select '102'
insert into tb select '103'

if object_id('temp') is not null
drop table temp
go
declare @sql varchar(8000)
set @sql='103,101,107'
set @sql='select * into temp from (select '+replace(@sql,',',' as id union all select ')+')tp'
exec(@sql)

declare @where varchar(8000)
select @where=isnull(@where+' or ','')+'charindex('''+ltrim(id)+''',s)>0'
from temp
exec('select * from tb where '+@where)

 

 

 

alter PROCEDURE Pager
/*Author:[e-mail]*/
(
 @tbName varchar(255),   --*表名
 @Order varchar(255),   --*排序,不要加order by eg:id desc
 @PageSize   int = 20,   --*页尺寸
 @PageNo  int = 1,   --*页码
 @Where  varchar(2000) = '',  --查询条件,不要加 where
 @GetFields varchar(1000) = '*', --需要返回的列
 @CountRows int=0 output,  --返回记录总数'非零时表示传入的总数
 @CountPage int=0 output  --返回总页数
)
AS
declare @strSQL   nvarchar(4000) -- 主语句
declare @OrderAsc nvarchar(400)  --缺省排序
declare @OrderDesc nvarchar(400) --缺省的相反排序
declare @fieldorder nvarchar(400) --排序的字段
declare @fullfields nvarchar(400) --所有获取的字段(包含返回的列和排序的列)
if @Where=''
 select @Where='1=1'
select @OrderAsc=''
while charindex(',',@Order)>0
 begin
    if @OrderAsc=''
  begin
     if charindex(' desc',substring(@Order,1,charindex(',',@Order)-1))=0 and charindex(' asc',substring(@Order,1,charindex(',',@Order)-1))=0
        select @OrderAsc=substring(@Order,1,charindex(',',@Order)-1)+' asc'
     else
       select @OrderAsc=substring(@Order,1,charindex(',',@Order)-1)
  end
    else
  begin
         if charindex(' desc',substring(@Order,1,charindex(',',@Order)-1))=0 and charindex(' asc',substring(@Order,1,charindex(',',@Order)-1))=0
       select @OrderAsc=@OrderAsc+','+substring(@Order,1,charindex(',',@Order)-1)+' asc'
    else
      select @OrderAsc=@OrderAsc+','+substring(@Order,1,charindex(',',@Order)-1)
  end
    select @Order=stuff(@Order,1,charindex(',',@Order),'')
 end
if @OrderAsc=''
   begin
 if charindex(' desc',@Order)=0 and charindex(' asc',@Order)=0
      select @OrderAsc=@Order+' asc'
 else
      select @OrderAsc=@Order
   end
else
   begin
 if charindex(' desc',@Order)=0 and charindex(' asc',@Order)=0
      select @OrderAsc=@OrderAsc+','+@Order+' asc'
 else
      select @OrderAsc=@OrderAsc+','+@Order
   end

select @OrderDesc=replace(replace(replace(@OrderAsc,' desc',' as_'),' asc',' desc'),' as_',' asc')
select @fieldorder=replace(replace(@OrderAsc,' desc',''),' asc','')
select @fullfields=@GetFields
if @fullfields<>'*'
  begin
 while charindex(',',@fieldorder)>0
 begin
  if charindex(','+substring(@fieldorder,1,charindex(',',@fieldorder)-1)+',',','+@fullfields+',')=0
   select @fullfields=@fullfields+','+substring(@fieldorder,1,charindex(',',@fieldorder)-1)
  select @fieldorder=stuff(@fieldorder,1,charindex(',',@fieldorder),'')
 end
 if charindex(','+@fieldorder+',',','+@fullfields+',')=0
     select @fullfields=@fullfields+','+@fieldorder
  end
--print @fullfields

if @CountRows=0
 begin
 -- 统计记录总数
  set @strSQL = 'select @a=count(1) from [' + @tbName + '] where ' + @Where

 exec sp_executesql @strSQL,N'@a int output',@CountRows output
 --print @CountRows
 
 -----返回总页数
 if @CountRows <= @PageSize
     set @CountPage = 1
 else
 begin
     set @CountPage = @CountRows/@PageSize
     if (@CountRows%@PageSize) > 0
        set @CountPage = @CountPage + 1
 end
 --print @CountPage
 end
else
   begin
    -----返回总页数
 if @CountRows <= @PageSize
     set @CountPage = 1
 else
 begin
     set @CountPage = @CountRows/@PageSize
     if (@CountRows%@PageSize) > 0
        set @CountPage = @CountPage + 1
 end
 --print @CountPage
   end

if @PageNo = 1
 begin
     set @strSQL = 'select top ' + str(@PageSize) +' '+@GetFields+ '  from [' + @tbName + '] where ' + @Where + ' order by ' + @OrderAsc
 end

if @PageNo>@CountPage
   select @PageNo=@CountPage
 
if @PageNo>1 and @PageNo=@CountPage
  begin
    set @strSQL='select '+@GetFields+' from (select top '+str(@CountRows-(@CountPage-1)*@PageSize)+' '+@fullfields+' from [' + @tbName + '] where ' + @Where + ' order by '+@OrderDesc+') A order by '+@OrderAsc
  end
 
if @PageNo>1 and @PageNo<@CountPage
   begin
 if @PageNo * 2 > @CountPage
  begin
   set @strSQL='select top '+str(@PageSize)+' '+@GetFields+' from (select top '+str(@CountRows-(@PageNo-1)*@PageSize)+' '+@fullfields+' from [' + @tbName + '] where ' + @Where + ' order by '+@OrderDesc+') A order by '+@OrderAsc+''
  end
 else
  begin
   set @strSQL='select '+@GetFields+' from (select top '+str(@PageSize)+' '+@fullfields+' from (select top '+str(@PageNo*@PageSize)+' '+@fullfields+' from [' + @tbName + '] where ' + @Where + ' order by '+@OrderAsc+') A order by '+@OrderDesc+') B order by '+@OrderAsc
  end
   end
 
 --print @strSQL
 exec (@strSQL)
go

 

 

 

Make MsSql pd的几种方式比较if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Paging_Asc_Desc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Paging_Asc_Desc]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Paging_Cursor]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Paging_Cursor]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Paging_RowCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Paging_RowCount]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Paging_SubQuery]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Paging_SubQuery]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

 

CREATE PROCEDURE Paging_Asc_Desc (
@Tables varchar(1000),
@PK varchar(100),
@Sort varchar(200) = NULL,
@PageNumber int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL)
AS

/*Find the @PK type*/
DECLARE @PKTable varchar(100)
DECLARE @PKName varchar(100)
DECLARE @type varchar(100)
DECLARE @prec int

IF CHARINDEX('.', @PK) > 0
BEGIN
SET @PKTable = SUBSTRING(@PK, 0, CHARINDEX('.',@PK))
SET @PKName = SUBSTRING(@PK, CHARINDEX('.',@PK) + 1, LEN(@PK))
END
ELSE
BEGIN
SET @PKTable = @Tables
SET @PKName = @PK
END

SELECT @type=t.name, @prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @PKTable AND c.name = @PKName

IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'

DECLARE @strPageSize varchar(50)
DECLARE @strRows varchar(50)
DECLARE @strFilter varchar(8000)
DECLARE @strGroup varchar(8000)
DECLARE @strSortColumn varchar(4000)
DECLARE @strSortDesc varchar(4000)

/*Default Sorting*/
IF @Sort IS NULL
SET @Sort = @PK

/*Set sorting variables.*/
IF CHARINDEX('DESC',@Sort)>0
BEGIN
SET @strSortDesc = REPLACE(@Sort, 'DESC', 'ASC')
SET @strSortColumn = ', ' + REPLACE(@Sort, 'DESC', '')
END
ELSE
BEGIN
IF CHARINDEX('ASC', @Sort) = 0
BEGIN
SET @strSortDesc = @Sort + ' DESC'
SET @strSortColumn = ', ' + @Sort
END
ELSE
BEGIN
SET @strSortDesc = REPLACE(@Sort, 'ASC', 'DESC')
SET @strSortColumn = ', ' + REPLACE(@Sort, 'ASC', '')
END
END
IF @Sort = @PK
SET @strSortColumn = ''

/*Default Page Number*/
IF @PageNumber < 1
SET @PageNumber = 1

/*Set paging variables.*/
SET @strPageSize = CONVERT(varchar(50), @PageSize)
SET @strRows = CONVERT(varchar(50), (@PageSize * (@PageNumber - 1) + @PageSize))

/*Set filter & group variables.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
END
ELSE
BEGIN
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''

/*Execute dynamic query*/
EXEC(
'DECLARE @tblPK TABLE (
PK ' + @type + ' NOT NULL PRIMARY KEY
)

INSERT INTO @tblPK SELECT TOP ' + @strPageSize + ' ' + @PK + ' FROM (SELECT TOP ' + @strRows + ' ' + @PK + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ') AS ' + @PKTable + ' ORDER BY ' + @strSortDesc + '

SELECT ' + @Fields + ' FROM ' + @Tables + ' JOIN @tblPK tblPK ON ' + @PK + ' = tblPK.PK ' + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

 

CREATE PROCEDURE Paging_Cursor (
@Tables varchar(1000),
@PK varchar(100),
@Sort varchar(200) = NULL,
@PageNumber int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL)
AS

/*Find the @PK type*/
DECLARE @PKTable varchar(100)
DECLARE @PKName varchar(100)
DECLARE @type varchar(100)
DECLARE @prec int

IF CHARINDEX('.', @PK) > 0
BEGIN
SET @PKTable = SUBSTRING(@PK, 0, CHARINDEX('.',@PK))
SET @PKName = SUBSTRING(@PK, CHARINDEX('.',@PK) + 1, LEN(@PK))
END
ELSE
BEGIN
SET @PKTable = @Tables
SET @PKName = @PK
END

SELECT @type=t.name, @prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @PKTable AND c.name = @PKName

IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'

DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strGroup varchar(1000)

/*Default Sorting*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PK

/*Default Page Number*/
IF @PageNumber < 1
SET @PageNumber = 1

/*Set paging variables.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50))

/*Set filter & group variables.*/
IF @Filter IS NOT NULL AND @Filter != ''
SET @strFilter = ' WHERE ' + @Filter + ' '
ELSE
SET @strFilter = ''
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''

/*Execute dynamic query*/
EXEC(
'DECLARE @PageSize int
SET @PageSize = ' + @strPageSize + '

DECLARE @PK ' + @type + '
DECLARE @tblPK TABLE (
PK ' + @type + ' NOT NULL PRIMARY KEY
)

DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT ' + @PK + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '

OPEN PagingCursor
FETCH RELATIVE ' + @strStartRow + ' FROM PagingCursor INTO @PK

SET NOCOUNT ON

WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
BEGIN
INSERT @tblPK (PK) VALUES (@PK)
FETCH NEXT FROM PagingCursor INTO @PK
SET @PageSize = @PageSize - 1
END

CLOSE PagingCursor
DEALLOCATE PagingCursor

SELECT ' + @Fields + ' FROM ' + @Tables + ' JOIN @tblPK tblPK ON ' + @PK + ' = tblPK.PK ' + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort
)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Paging_RowCount
(
@Tables varchar(1000),
@PK varchar(100),
@Sort varchar(200) = NULL,
@PageNumber int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL)
AS

/*Default Sorting*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PK

/*Find the @PK type*/
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int

/*Set sorting variables.*/
IF CHARINDEX('DESC',@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '<='
END
ELSE
BEGIN
IF CHARINDEX('ASC', @Sort) = 0
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
SET @operator = '>='
END

IF CHARINDEX('.', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END

SELECT @type=t.name, @prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName

IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'

DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)

/*Default Page Number*/
IF @PageNumber < 1
SET @PageNumber = 1

/*Set paging variables.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50))

/*Set filter & group variables.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''

/*Execute dynamic query*/
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
'
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

 

CREATE PROCEDURE Paging_SubQuery (
@Tables varchar(1000),
@PK varchar(100),
@Sort varchar(200) = NULL,
@PageNumber int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL)
AS

DECLARE @strPageSize varchar(50)
DECLARE @strSkippedRows varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)

/*Default Sorting*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PK
SET @Sort = ' ORDER BY ' + @Sort + ' '

/*Default Page Number*/
IF @PageNumber < 1
SET @PageNumber = 1

/*Set paging variables.*/
SET @strPageSize = CONVERT(varchar(50), @PageSize)
SET @strSkippedRows = CONVERT(varchar(50), @PageSize * (@PageNumber - 1))

/*Set filter & group variables.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''

IF @PageNumber = 1 -- In this case we can execute a more efficient query with no subqueries.
EXEC (
'SELECT TOP ' + @strPageSize + ' ' + @Fields + ' FROM ' + @Tables +
@strFilter + @strGroup + @Sort
)
ELSE -- Execute a structure of subqueries that brings the correct page.
EXEC (
'SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @PK + ' IN ' + '
(SELECT TOP ' + @strPageSize + ' ' + @PK + ' FROM ' + @Tables +
' WHERE ' + @PK + ' NOT IN
(SELECT TOP ' + @strSkippedRows + ' ' + @PK + ' FROM ' + @Tables +
@strFilter + @strGroup + @Sort + ') ' +
@strSimpleFilter +
@strGroup +
@Sort + ') ' +
@strGroup +
@Sort
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SQL内置函数
CREATE function [dbo].[MoneytoChinese](@ChangeMoney Money)
returns nvarchar(50) as
begin
Declare @String1 char(20)
Declare @String2 char(30)
Declare @String4 Varchar(100)
Declare @String3 Varchar(100) --从原A值中取出的值
Declare @i bigint --循环变量
Declare @J bigint --A的值乘以100的字符串长度
Declare @Ch1 Varchar(100) --数字的汉语读法
Declare @Ch2 Varchar(100) --数字位的汉字读法
Declare @Zero bigint --用来计算连续有几个零
Declare @ReturnValue VarChar(100)
Select @ReturnValue = ''
Select @String1 = '零壹贰叁肆伍陆柒捌玖'
Select @String2 = '万仟佰拾亿仟佰拾万仟佰拾元角分'

Select @String4 = Cast(@ChangeMoney*100 as bigint)

select @J=len(cast((@ChangeMoney*100) as bigint))

Select @String2=Right(@String2,@J)

Select @i = 1

while @i<= @j Begin

Select @String3 = Substring(@String4,@i,1)

if @String3<>'0' Begin

Select @Ch1 = Substring(@String1, Cast(@String3 as bigint) + 1, 1)
Select @Ch2 = Substring(@String2, @i, 1)
Select @Zero = 0 --表示本位不为零
end
else Begin
If (@Zero = 0) Or (@i = @J - 9) Or (@i = @J - 5) Or (@i = @J - 1)
Select @Ch1 = '零'
Else
Select @Ch1 = ''

Select @Zero = @Zero + 1 --表示本位为0

--如果转换的数值需要扩大，那么需改动以下表达式 I 的值。

If @i = @J - 10 Begin
Select @Ch2 = '亿'
Select @Zero = 0
end

If @i = @J - 6 Begin
Select @Ch2 = '万'
Select @Zero = 0
end

if @i = @J - 2 Begin
Select @Ch2 = '元'
Select @Zero = 0
end

If @i = @J
Select @Ch2 = '整'

end

Select @ReturnValue = @ReturnValue + @Ch1 + @Ch2

select @i = @i+1
end

--最后将多余的零去掉
If CharIndex('仟仟',@ReturnValue) <> 0
Select @ReturnValue = Replace(@ReturnValue, '仟仟', '仟')

If CharIndex('佰佰',@ReturnValue) <> 0
Select @ReturnValue = Replace(@ReturnValue, '佰佰', '佰')

If CharIndex('零元',@ReturnValue) <> 0
Select @ReturnValue = Replace(@ReturnValue, '零元', '元')

If CharIndex('零万',@ReturnValue) <> 0
Select @ReturnValue = Replace(@ReturnValue, '零万', '万')

If CharIndex('零亿',@ReturnValue) <> 0
Select @ReturnValue = Replace(@ReturnValue, '零亿', '亿')

If CharIndex('零整',@ReturnValue) <> 0
Select @ReturnValue = Replace(@ReturnValue, '零整', '整')

If CharIndex('零佰',@ReturnValue) <> 0
Select @ReturnValue = Replace(@ReturnValue, '零佰', '零')

If CharIndex('零仟',@ReturnValue) <> 0
Select @ReturnValue = Replace(@ReturnValue, '零仟', '零')

If CharIndex('元元',@ReturnValue) <> 0
Select @ReturnValue = Replace(@ReturnValue, '元元', '元')

return @ReturnValue
end