CODEWITHSUNDEEP
sqloracle-database
Bogdan
Bogdan

Reputation: 713

Sort by first digits of a number column

I have a table:

CREATE TABLE client_details(
   ...
   clientid number(10,0) ,
   name varchar2,
   ...
)

Where clientid could be some unique number (external id from another source).

I need to implement filtering by clientd, so a system will display all records where clientid begins with user-inputed value

For example:

let the table contains the following data:

[{clientId=119, name='Bob'},
 {clientId=11234,name='Anna'},
 {clientId=2,name='Eva'}]

When a user inputs "11" as a search query, then the system will show:

[{clientId=119, name='Bob'},
 {clientId=11234,name='Anna'}]

When a user inputs "2" as a search query, then the system will show:

 [{clientId=2, name='Eva'}]

A user can input any number from 0 to 999999999 as a query.

My original intention is to use something like

SELECT * 
FROM client_details
WHERE SUBSTR(client, 0, length_of_query) = 'query'

(where query stands for user input and length_of_query stands for query lengths as a string)

I a bit dislike substr and converting int to string in where, s.a. the table could be quite big. On the other hand I would prefer not to change DB structure.

Maybe someone has a better idea?

Upvotes: 0

Views: 448

Answers (2)

Sohail
Sohail

Reputation: 574

Perform CONVERSION using(CAST or CONVERT) and use LIKE operator but Do read these Tutorial before changing datatype. 

SELECT *  
FROM [Clients_details]  
WHERE CAST(clientId AS Varchar(50)) LIKE 'query%';

Upvotes: 1

Colin 't Hart
Colin 't Hart

Reputation: 7729

Cast to a string and use the like operator:

SELECT *
FROM client_details
WHERE TO_CHAR(clientId) LIKE <<query>> || '%'

Create an index on client_details(TO_CHAR(clientId)) and you'll get great performance too when the users type more than a few numbers.

Upvotes: 3

Related Questions