0

Would like to check with all expect in SQL query. Please look at below for the examples. Thanks.

Column
abc 123
cdf 456

What I want is:

123
456

Appreciate your help for this question. Thanks a lot!

CK Ang
  • 111
  • 11
  • is this format strictly followed? <3 chars> <3 digits> – swayamraina Sep 18 '18 at 09:03
  • @swayamraina No, it can be more than 3 chars and more than 3 digits – CK Ang Sep 18 '18 at 09:04
  • Then you need to specify exactly the rules you want to implement. Can you ever have `abc123xyz456`? And if so, do you want `123` or `456` or `123456` as your result? Are the characters always `a-zA-Z` or can you also have `,.!"£$%`, etc? You need to tell us ***everything*** you possibly can about the input you are working with and the output you want from it. The less complete your description the more likely any solution will break for some of your data. – MatBailie Sep 18 '18 at 09:11
  • you should try solution given here "https://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string" – Raj Sep 18 '18 at 09:12
  • @MatBailie the character will always show in front and the numbers will be behind. Eg: testing 45454.1, testing 32434. – CK Ang Sep 18 '18 at 09:13
  • Again, that's making assumptions you think we'll "just understand". Be specific... For `testing 45454.1` extracting "just numbers" from the end would yield `1`. Are you expecting, however, to be aware of the decimal point and want `45454.1`? In which case is `abcxyz.123` ever possible? Should it return `.123` or `123`? – MatBailie Sep 18 '18 at 09:16
  • 2
    Also, what dialect of SQL are you using? I can think of a few potentially good solutions, but the vary depending on whether you're using SQL Server, MySQL, Oracle, SQLite, PostgreSQL, etc, etc. – MatBailie Sep 18 '18 at 09:24
  • https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=029777ce0320e2102228d1ffbca5e332 – MatBailie Sep 18 '18 at 09:58

1 Answers1

0

Based on your comments, Try this - 

SELECT REGEXP_SUBSTR(Column, '[0-9]+');
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40