CODEWITHSUNDEEP
sqlsql-server
Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

Query to find results starting with a number or higher - SQL Server

Example: http://sqlfiddle.com/#!18/7f3df/2

CREATE TABLE Table1 (
Reg uniqueidentifier
);

INSERT INTO Table1 (Reg)
Values
('DF215E10-8BD4-4401-B2DC-99BB03135F2E'),
('93015E10-8BD4-4401-B2DC-99BB03135F2E'),
('21215E10-8BD4-4401-B2DC-99BB03135F2E');

Query:

select * from Table1
WHERE left(CAST(Reg AS CHAR(36)),1) > 8

I need to create a query that finds all results that start with either a number 8 or higher or starts with a letter but i am getting a conversion error i cant find a fix for. The column is a uniqueidentifier. How can i cast this to something i can filter on?

Can anyone give some advice on a solution to this?

Thanks

Upvotes: 4

Views: 53

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You need to do the comparison using strings:

convert(varchar(36), newid()) like '[89ABCDEF]%'

Upvotes: 8

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521437

You may use SQL Server's enhanced LIKE here, which has some basic regex support:

SELECT *
FROM table1
WHERE Reg LIKE '[89A-Z]%';

The pattern [89A-Z]% says to match a starting 8 or 9, or any letter.

Upvotes: 5

Related Questions