I want to find strings of any length that contain only 0's and a symbol such as a / a . or a - Examples include 0__0 and 000/00/00000 and .00000

CodePudding user response：

Considering this sample data:

CREATE TABLE dbo.things(thing varchar(255));

INSERT dbo.things(thing) VALUES
('0__0'),('000/00/00000'),('00000'),('0123456');

Try the following, which locates the first position of any character that is NOT a 0, a decimal, a forward slash, or an underscore. PATINDEX returns 0 if the pattern is not found.

SELECT thing FROM dbo.things
WHERE PATINDEX('%[^0^.^/^_]%', thing) = 0;

Results:

The opposite:

SELECT thing FROM dbo.things
WHERE PATINDEX('%[^0^.^/^_]%', thing) > 0;

Results:

• Example db<>fiddle

CodePudding user response：

I can see a way of doing this... But it's something that wouldn't perform well, if you think about using it as a search criteria.

We are going to use a translate function on SQL Server, to replace the allowed characters, or symbols as you've said, with a zero. And then, eliminates the zeroes. If the result is an empty string, then there are two cases, or it only had zeroes and allowed characters, or it already was an empty string.

So, checking for this and for non-empty strings, we can define if it matches your criteria.

-- Test scenario
create table #example (something varchar(200) )
insert into #example(something) values
--Example cases from Stack Overflow
('0__0'),('000/00/00000'),('.00000'),
-- With something not allowed (don't know, just put a number)
('1230__0'),('000/04560/00000'),('.00000789'),
-- Just not allowed characters, zero, blank, and NULL
('1234567489'),('0'), (''),(null)

-- Shows the data, with a column to check if it matches your criteria
select *
from #example e
cross apply (
    select case when
        -- If it *must* have at least a zero
        e.something like '%0%' and
        -- Eliminates zeroes
        replace(
            -- Replaces the allowed characters with zero
            translate(
                e.something
                ,'_./'
                ,'000'
            )
            ,'0'
            ,''
        ) = ''
    then cast(1 as bit)
    else cast(0 as bit)
    end as doesItMatch
) as criteria(doesItMatch)

I really discourage you from using this as a search criteria.

-- Queries the table over this criteria.
-- This is going to compute over your entire table, so it can get very CPU intensive
select *
from #example e
where
-- If it *must* have at least a zero
e.something like '%0%' and
-- Eliminates zeroes
replace(
    -- Replaces the allowed characters with zero
    translate(
        e.something
        ,'_./'
        ,'000'
    )
    ,'0'
    ,''
) = ''

If you must use this as a search criteria, and this will be a common filter on your application, I suggest you create a new bit column, to flag if it matches this, and index it. Thus, the increase in computational effort would be spread on the inserts/updates/deletes, and the search queries won't overloading the database.

The code can be seen executing here, on DB Fiddle.

CodePudding user response：

What I got from the question is that the strings must contain both 0 and any combination of the special characters in the string.

If you have SQL Server 2017 and above, you can use translate() to replace multiple characters with a space and compare this with the empty string. Also you can use LIKE to enforce that both a 0 and any combination of the special character(s) appear at least once:

DECLARE @temp TABLE (val varchar(100))

INSERT INTO @temp VALUES
('0__0'), ('000/00/00000'), ('.00000'), ('w0hee/'), ('./')

SELECT *
  FROM @temp
 WHERE val LIKE '%0%' --must have at least one zero somewhere
   AND val LIKE '%[_/.]%' --must have at least one special character(s) somewhere
   AND TRANSLATE(val, '0./_', '    ') = '' --translated zeros and sp characters to spaces equivalent to an empty string

Creates output:

val
0__0
000/00/00000
.00000