获取不满足喜欢的角色列表 [英] Get list of character which not satisfy Like

问题描述

我需要不满足条件的字符列表，例如，

I need list of character that not satisfy like condition for example,

--Validation for X
declare @test varchar(50)
set @test='ds[fds'
if @test like '%[^0-9a-zA-Z .()/-]%'
print 'invalid -yes'
else
print 'invalid -no'

在上面的情况下，我需要输出'['，如果有多个无效字符，则不满足条件需要所有

In above case i need output '[' which is not satisfy condition also if there is more than one invalid character then need that all

declare @phone varchar(50)
set @phone='212@@f'

select  REPLACE(@phone
               , SUBSTRING(@phone, PATINDEX('%[^0-9a-zA-Z .()/-]%', @phone), 1)
              , '')

但是如果输入字符串中有'％'这样的sql字符则不起作用，那么解决方案是什么。

but it does not work if there is sql character like '%' in input string so what is the solution.

declare @phone varchar(50)
set @phone='212%@@f'

select  REPLACE(@phone
               , SUBSTRING(@phone, PATINDEX('%[^0-9a-zA-Z .()/-]%', @phone), 1)
              , '')

推荐答案

declare @phone varchar(50)
set @phone='212%@@f'

declare @illegal varchar(50)
declare @test varchar(50)

set @illegal = SUBSTRING(@phone, PATINDEX('%[^0-9a-zA-Z .()/-]%', @phone), 1)
set @test = REPLACE(@phone, SUBSTRING(@phone, PATINDEX('%[^0-9a-zA-Z .()/-]%', @phone), 1), '')

while @test <> @phone
    begin
    set @phone = @test
    set @illegal = @illegal + SUBSTRING(@phone, PATINDEX('%[^0-9a-zA-Z .()/-]%', @phone), 1)
    set @test = REPLACE(@phone, SUBSTRING(@phone, PATINDEX('%[^0-9a-zA-Z .()/-]%', @phone), 1), '')
    end

select @illegal
select @phone

这篇关于获取不满足喜欢的角色列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！