CODEWITHSUNDEEP
sqlpostgresql
Markie Doe
Markie Doe

Reputation: 125

SQL substring get all digits before certain character

The result in a Column is:

14800:DATAB Unload

I'm trying to get all the digits before the : so i receive only the number.

What should i put in my select to get this done?

Upvotes: 0

Views: 1139

Answers (2)

user330315
user330315

Reputation:

You can use a regular expression for that: 

select regexp_replace(the_column, '(^[0-9]+):.*', '\1')
from the_table;

Or use the left() function:

select left(the_column, strpos(col, ':') - 1)
from the_table;

The regex is more robust against strings that do not contain a number or a :. If you are certain that every value will have a : in it, the second solution should be preferred as it is much faster.

The following example: 

with data (col) as (
  values ('14800:DATAB Unload'), ('Another Unload')
)
select regexp_replace(col, '(^[0-9]+):.*', '\1'), 
       left(col, strpos(col, ':') - 1)
from data;

returns:

regexp_replace  | left       
----------------+------------
14800           | 14800      
Another Unload  | Another Unloa

The second value will be left unchanged by the regex solution, but will cut off the last character when using left().

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

for mysql 

SELECT LEFT(you_column,LOCATE(':',your_column) - 1) from your_table

for postgresql

SELECT substr(you_column , 1,  position( ':' in  your_column) - 1) from your_table

or 

SELECT left(you_column , position( ':' in  your_column) - 1) from your_table

Upvotes: 1

Related Questions