Sql server query for numbers inside a square bracket

I have few formats of records in a table, ABCDEF, [123]ABCDEF, ABCDEF[ABC] and ABCDEF[123] Numbers of letters and digits are not fixed, they vary.

I can easily find the ones with brackets by something like this and it's variations.

SELECT * FROM X WHERE Y LIKE '%/[%' ESCAPE '/'

But I want to find the records where there are only three numbers inside the brackets and only if they appear at the beginning or ending of the line. i.e [123]ABCDEF and ABCDEF[123] formats.

I tried LIKE '%[___/]' ESCAPE '/' and hoped to manipulate it only accepting numbers inside the bracket but wasn't able to make it work.

A huge caveat is there might be records such as ABC[123]DEF format. This might troublesome because I don't want such records returned. I don't know if there are such records but I can't dismiss the possibility.

How can I do what I'm trying to do?

Solution

Brackets are used to provide valid set of characters. [0-9] means any digit from 0 to 9. If bracket is used literally, it must be escaped:

DECLARE @t TABLE(T nvarchar(20))

INSERT @t values ('ABCDEF'), ('[123]ABCDEF'), ('ABCDEF[ABC]'), ('ABCDEF[123]'), ('AS[123]AS')

SELECT *
FROM @t
WHERE T LIKE '\[[0-9][0-9][0-9]\]%' ESCAPE '\'
   OR T LIKE '%\[[0-9][0-9][0-9]\]' ESCAPE '\'

--Or match anywhere
--WHERE T LIKE '%\[[0-9][0-9][0-9]\]%' ESCAPE '\'

Result

T
--------------------
[123]ABCDEF
ABCDEF[123]