order result selected by LIKE

Question

Post reply

order result selected by LIKE

Teutales01

Mr or Mrs. 500

Points: 522
More actions
November 23, 2010 at 4:07 am

#229035

I Select some data with:
SELECT *
FROM custom.Routing
WHERE @var LIKE Number + '%'
Field Number contains for example: 999 and 99912
When I search for @var = 99912 I get two lines: 999 and 99912
I want an order in my result:
99912, 5 matches
999, 3 matches
matches == count of matching digits

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Something like this ?

Be warned though , this is non-sargable ,and will perform badly over a large dataset.

SELECT number,count(*)

FROM custom.Routing

WHERE @var LIKE Number + '%'

group by number

order by count(*) desc

Clear Sky SQL
My Blog[/url]

Teutales01 Mr or Mrs. 500 Points: 522 More actions · Answer 2

no, this counts the rows...

I need the information about the biggest hit rate.

When I search for 99911 both rows matches, 999 and 99911. But the hit rate of 99911 is higher (5)...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

wagner-670519 (11/23/2010)
no, this counts the rows...
I need the information about the biggest hit rate.
When I search for 99911 both rows matches, 999 and 99911. But the hit rate of 99911 is higher (5)...

wagner-670519 (11/23/2010)
matches == count of matching digits

So, what's the difference between matches and hit rate?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 4

Maybe:

ORDER BY LEN( Number ) DESC

Scott Pletcher, SQL Server MVP 2008-2010

Teutales01 Mr or Mrs. 500 Points: 522 More actions · Answer 5

Sorry about my bad explanation. I try to specify it.

I have a table which contains the first digits of telephone numbers (column Number)

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'custom') BEGIN

EXEC (N'CREATE SCHEMA custom')

END

GO

-------------------------------------------------------------------------------

IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'custom' AND TABLE_NAME = 'Routing' ) BEGIN

CREATE TABLE custom.Routing (

[GUID]uniqueidentifier NOT NULL,

ClientsGUIDuniqueidentifier NOT NULL,

GroupsGUIDuniqueidentifier NOT NULL,

Numbervarchar(32) NOT NULL,

CONSTRAINTPK_Routing PRIMARY KEY CLUSTERED([GUID])

/* you do not have this tables,

CONSTRAINTFK_Routing_Clients FOREIGN KEY(ClientsGUID) REFERENCES common.Clients ([GUID]),

CONSTRAINTFK_Routing_Groups FOREIGN KEY(GroupsGUID) REFERENCES common.Groups ([GUID]) ON DELETE CASCADE

*/

)

END

GO

Let's put some data in it....

INSERT INTO custom.Routing ([GUID],ClientsGUID,GroupsGUID,Number) VALUES (NEWID(),'00000000-0000-0000-0000-000000000001','00000000-0000-0000-0000-000000000001','0180')

INSERT INTO custom.Routing ([GUID],ClientsGUID,GroupsGUID,Number) VALUES (NEWID(),'00000000-0000-0000-0000-000000000001','00000000-0000-0000-0000-000000000002','01')

INSERT INTO custom.Routing ([GUID],ClientsGUID,GroupsGUID,Number) VALUES (NEWID(),'00000000-0000-0000-0000-000000000001','00000000-0000-0000-0000-000000000003','02')

The result is this:

GUID ClientsGUID GroupsGUID Number

------------------------------------ ------------------------------------ ------------------------------------ --------------------------------

04F9310E-3D9B-4A3E-A437-7FA37BAF54A9 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000003 02

D374CCE2-92B1-4FDA-B9E3-950249A7A2C7 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000002 01

5A7939DA-CEB6-473A-BA7C-B29F5E8B0F82 00000000-0000-0000-0000-000000000001 00000000-0000-0000-0000-000000000001 0180

(3 Zeile(n) betroffen)

Now I put a stored procedure in it (here a simplified version)

CREATE PROCEDURE custom.Routing_getGroupsGUIDbyNumber

@caller NVARCHAR(32)

AS

SELECT GroupsGUID FROM custom.Routing WHERE @caller LIKE Number + '%'

RETURN

GO

Now I fire my query:

EXEC custom.Routing_getGroupsGUIDbyNumber '0180'

The result is

GroupsGUID

------------------------------------

00000000-0000-0000-0000-000000000002

00000000-0000-0000-0000-000000000001

(2 Zeile(n) betroffen)

because

@caller LIKE Number + '%'

0180 LIKE 0180% AND 0180 LIKE 01%

My question is: I need an order for this result, like this:

GroupsGUID matchedDigits

------------------------------------ ------------------------------------

00000000-0000-0000-0000-000000000002 2

00000000-0000-0000-0000-000000000001 4

(2 Zeile(n) betroffen)

because 01 are 2 digits and 0180 are 4 digits...

greetz...

tommyh SSCertifiable Points: 6302 More actions · Answer 6

Okay im sure im totally missing something here but adding either

ORDER BY Convert(integer, number)

or

ORDER BY Len(Number)

or

ORDER BY Number

gives me the result that your after.

Add

Len(Number) matchedDigits

to the select and its exactly the same result as your example.

Maybe your example SP is to simplified? And its really something else thats causing problems?

/T

Teutales01 Mr or Mrs. 500 Points: 522 More actions · Answer 7

oh my god. sure you all are right. I think it it was too easy to solve it by myself.

there are some bugs in my head...