Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Sql like with without leading 0's

I have a table like this

Id  Number (string)

1   0000231
2   00232
3   231
4   24
5   A1233

Ex: I need to search by value 23 ( may vary)

Expected Result:

Id  Number

1   0000231
2   00232
3   23

Current Ef core search code

Where(c => EF.Functions.Like(c.AIN, $"{filter}%"))

This is getting only 3rd record. Table has Number column which has string. But need to eliminate leading 0’s in like function.

i dont expect answer in Ef. i need Sql query to ]handle effienntly

Tags:

2

Answers


  1. 0

    If you can handle this using raw MySQL, then you could try just casting the column value from text to integer:

    SELECT Id, Number
FROM yourTable
WHERE CAST(Number AS UNSIGNED) = 23;
    Login or Signup to reply.
  2. 0

    In MySQL you can use the TRIM function:

    where trim(leading '0' from number) like  '23%'

    An alternate would be to use REGEXP operator:

    where number regexp '0*23'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search