1.计算一个字符串在其他字符串中出现的次数
--
计算inner_string在outer_string中出现的次数
if object_id ( ' COUNT_CHARACTERS ' , ' p ' ) is not null
drop proc COUNT_CHARACTERS
go
CREATE PROC COUNT_CHARACTERS
@outer_string VARCHAR ( 400 ),
@inner_string VARCHAR ( 50 )
AS
DECLARE @i INT ,
@n INT
SET @n = 0
WHILE CHARINDEX ( @inner_string , @outer_string ) <> 0
BEGIN
SELECT @i = CHARINDEX ( @inner_string , @outer_string )
SET @outer_string = SUBSTRING ( @outer_string ,( @i + LEN ( @inner_string )),
( LEN ( @outer_string ) - ( @i + LEN ( @inner_string ) - 1 )))
SET @n = @n + 1
END
select @n
GO
if object_id ( ' COUNT_CHARACTERS ' , ' p ' ) is not null
drop proc COUNT_CHARACTERS
go
CREATE PROC COUNT_CHARACTERS
@outer_string VARCHAR ( 400 ),
@inner_string VARCHAR ( 50 )
AS
DECLARE @i INT ,
@n INT
SET @n = 0
WHILE CHARINDEX ( @inner_string , @outer_string ) <> 0
BEGIN
SELECT @i = CHARINDEX ( @inner_string , @outer_string )
SET @outer_string = SUBSTRING ( @outer_string ,( @i + LEN ( @inner_string )),
( LEN ( @outer_string ) - ( @i + LEN ( @inner_string ) - 1 )))
SET @n = @n + 1
END
select @n
GO
2.得到字符串中起始点到终点之间的字符串,包括终点位置
--
得到字符串中起始点到终点之间的字符串,包括终点位置
if object_id ( ' GET_STRING_BETWEEN_TWO_CHARACTERS ' , ' p ' ) is not null
drop proc GET_STRING_BETWEEN_TWO_CHARACTERS
go
CREATE PROC GET_STRING_BETWEEN_TWO_CHARACTERS
@string VARCHAR ( 2000 ),
@start INT ,
@end INT
AS
DECLARE @output VARCHAR ( 2000 )
IF @start > LEN ( @string )
BEGIN
SELECT ' 起始点位置大于字符串长度 '
RETURN
END
ELSE
IF @start >= @end
BEGIN
SELECT ' 结束点位置小于起始点 '
RETURN
END
SELECT @output = SUBSTRING ( @string , @start ,( @end - @start + 1 ))
select @output
GO
if object_id ( ' GET_STRING_BETWEEN_TWO_CHARACTERS ' , ' p ' ) is not null
drop proc GET_STRING_BETWEEN_TWO_CHARACTERS
go
CREATE PROC GET_STRING_BETWEEN_TWO_CHARACTERS
@string VARCHAR ( 2000 ),
@start INT ,
@end INT
AS
DECLARE @output VARCHAR ( 2000 )
IF @start > LEN ( @string )
BEGIN
SELECT ' 起始点位置大于字符串长度 '
RETURN
END
ELSE
IF @start >= @end
BEGIN
SELECT ' 结束点位置小于起始点 '
RETURN
END
SELECT @output = SUBSTRING ( @string , @start ,( @end - @start + 1 ))
select @output
GO
3.字符串部分反向
字符串全部反向用函数REVERSE
if
object_id
(
'
REVERSE_ORDER
'
,
'
FN
'
)
is
not
null
drop function REVERSE_ORDER
go
CREATE FUNCTION REVERSE_ORDER
( @string VARCHAR ( 8000 ),
@start INT ,
@end INT )
RETURNS VARCHAR ( 8000 )
AS
BEGIN
IF @start > @end
BEGIN
RETURN ' 结束点位置小于起始点 '
END
IF @start > LEN ( @string )
OR @end > LEN ( @string )
BEGIN
RETURN ' 请在字符串的长度内取值 '
END
declare @OutPut varchar ( 8000 )
if @start > 1
begin
select @OutPut = SUBSTRING ( @string , 1 , @start - 1 )
select @OutPut = @OutPut + REVERSE ( SUBSTRING ( @string , @start ,( @end - @start + 1 )))
end
else begin
select @OutPut = REVERSE ( SUBSTRING ( @string , @start ,( @end - @start + 1 )))
end
if @end < len ( @string )
begin
select @OutPut = @OutPut + SUBSTRING ( @string , @end + 1 ,( LEN ( @string ) - @end ))
end
RETURN @OutPut
END
drop function REVERSE_ORDER
go
CREATE FUNCTION REVERSE_ORDER
( @string VARCHAR ( 8000 ),
@start INT ,
@end INT )
RETURNS VARCHAR ( 8000 )
AS
BEGIN
IF @start > @end
BEGIN
RETURN ' 结束点位置小于起始点 '
END
IF @start > LEN ( @string )
OR @end > LEN ( @string )
BEGIN
RETURN ' 请在字符串的长度内取值 '
END
declare @OutPut varchar ( 8000 )
if @start > 1
begin
select @OutPut = SUBSTRING ( @string , 1 , @start - 1 )
select @OutPut = @OutPut + REVERSE ( SUBSTRING ( @string , @start ,( @end - @start + 1 )))
end
else begin
select @OutPut = REVERSE ( SUBSTRING ( @string , @start ,( @end - @start + 1 )))
end
if @end < len ( @string )
begin
select @OutPut = @OutPut + SUBSTRING ( @string , @end + 1 ,( LEN ( @string ) - @end ))
end
RETURN @OutPut
END