CODEWITHSUNDEEP
sqlsql-serverregexstored-procedures
beginner
beginner

Reputation: 483

Zero or more occurrences of pattern matching in sql stored procedure

In my stored procedure, I want to set student ID pattern and further based on that pattern I want to retrieve data from table.

Student ID pattern should be like string STUD-, followed by 2 to 3 digits.

The pattern should have values from STUD-11 and STUD-125

For example: STUD-12 and STUD-123.

Currently I am using pattern as :

SET @stud_ID_like = 'STUD-[1-9][0123456789]'

But it accepts STUD-10 that I don't want.

And how can I get 3 digits after string STUD-.

For this if I use STUD-[1-9][0123456789][0123456789] pattern then how can I take 2 digit value like STUD-15.

Is there '*' wild card character in SQL for pattern matching so that I can use it for zero or more occurrences ? Or any other solution for this ?

My stored procedure is something like this:

stored procedure A
    @stud_ID_like varchar(128)
AS
BEGIN
    SET @stud_ID_like = 'STUD-[1-9][0123456789]'

    BEGIN TRANSACTION
        SELECT TOP 1 [Student Name]
        FROM [Student_list_table] 
        WHERE [Result] = 'PASS' AND [St_ID] LIKE @stud_ID_like 

    COMMIT TRANSACTION
END

Upvotes: 1

Views: 1906

Answers (3)

JamieD77
JamieD77

Reputation: 13949

You can build or statements for the matches between STUD-11 and STUD-125

SELECT TOP 1 [Student Name]
FROM [Student_list_table] 
WHERE [Result] = 'PASS' 
AND [St_ID] LIKE 'STUD-1[1-9]' 
    OR [St_ID] LIKE 'STUD-[2-9][0-9]'
    OR [St_ID] LIKE 'STUD-1[0-1][0-9]'
    OR [St_ID] LIKE 'STUD-12[0-5]'

SQL Fiddle Example

Might be less trouble to pass in a min id and max id, then convert the value after STUD- to an int and compare that way.

CREATE PROC A (
    @Min INT,
    @Max INT
)
AS
BEGIN
    SET @Min = 11
    SET @Max = 125

    SELECT TOP 1 [Student Name]
    FROM [Student_list_table] 
    WHERE [Result] = 'PASS' 
        AND CONVERT(INT, SUBSTRING([St_ID], PATINDEX('%-%', [St_ID]) + 1, LEN([St_ID]))) 
            BETWEEN @Min AND @Max
END

Upvotes: 2

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

This solution will not use index if any exists on StudentID column:

where replace(StudentID, 'STUD-', '') between 11 and 125

Upvotes: 1

LDMJoe
LDMJoe

Reputation: 1589

To meet the criteria as specified, you'd have to do 3 parts.

From the OP comment..

Except STUD-1 to STUD-10 , all other Values fromSTUD-11 to STUD-125 is valid values for me. And main thing is how can I check zero or more occurance so that I can use it for 3rd digit

WHERE
    (
        StudentID LIKE 'STUD-[1-9][0-9]' 
        AND 
        StudentID != 'STUD-10'
    )
    OR
    (
        StudentID LIKE 'STUD-[1-9][0-9][0-9]' 
    )

This will cover 11 - 999

You can continue nesting conditions like this if you want to forcibly exclude values >= 126, but it gets ugly. One of the other proposed answers takes a stab at it, but accidentally excluded all values over 100 that ended with a digit higher than 5.

Upvotes: 1

Related Questions