新增文章

文章标题分类内容

select * from (
   select 
          FNUMBER  ,dbo.a_NoNum( FNUMBER ) En 
         
         ,charIndex( left(dbo.a_NoNum( FNUMBER ),1) ,FNUMBER ,0 ) FStart

,case charIndex( left(dbo.a_NoNum( FNUMBER ),1) ,FNUMBER ,0 )
               when 1  then 2  
               else 1 
               end  Sort
               
         ,convert( decimal(23,10)
                  ,case when charIndex( left(dbo.a_NoNum( FNUMBER ),1) ,FNUMBER ,0 ) = 0 
                        then subString( FNUMBER ,1 ,len(FNUMBER) )  
                        when charIndex( left(dbo.a_NoNum( FNUMBER ),1) ,FNUMBER ,0 ) = 1
                        then '0'
                        else subString( FNUMBER ,1 ,charIndex( left(dbo.a_NoNum( FNUMBER ),1) ,FNUMBER ,0 ) - 1 ) 
                        end
                  ) Num

,case when charIndex( left(dbo.a_NoNum( FNUMBER ),1) ,FNUMBER ,0 ) != 0 
               then subString(FNUMBER ,charIndex( left(dbo.a_NoNum( FNUMBER ),1) ,FNUMBER ,0 ) ,len(FNUMBER) )  
               else '' 
               end  Txt
   
   from T_BAS_ASSISTANTDATAENTRY  where FID = '6440e2d137ee77'
   ) a
   order by Sort,Num,Txt

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--【函数】 删除

if exists ( select * from sys.objects where object_id = object_id(N'dbo.a_NoNum') and type in (N'FN', N'if', N'TF', N'FS', N'FT') )
               drop     function                                       dbo.a_NoNum
   go
   
   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--【函数】 dbo.a_NoNum( 'xxx' )  去除数字

create function dbo.a_NoNum
   (
       @Txt varchar(max) 
   )
   returns varchar(max) 
   as 
   begin
   ---------------------------------------------------------------------------------------------------------------------------------------------
      set @Txt = replace( @Txt ,'.' ,'')

set @Txt = replace( @Txt ,'1' ,'')
      set @Txt = replace( @Txt ,'2' ,'')
      set @Txt = replace( @Txt ,'3' ,'')
      set @Txt = replace( @Txt ,'4' ,'')
      set @Txt = replace( @Txt ,'5' ,'')
      set @Txt = replace( @Txt ,'6' ,'')
      set @Txt = replace( @Txt ,'7' ,'')
      set @Txt = replace( @Txt ,'8' ,'')
      set @Txt = replace( @Txt ,'9' ,'')
      set @Txt = replace( @Txt ,'0' ,'')
   ---------------------------------------------------------------------------------------------------------------------------------------------
      return @Txt
   end
   
/*
------------------------------------------------------------------------------------------------------------------------------------------------------------------------

select dbo.a_NoNum( '金额B(+本23位.币-3，)$as2#０．６ＢＣＤ' )   -- 金额B(+本位币-，)$as#ＢＣＤ
   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
*/
go