查找字符串中的数字位置 [英] Finding number position in string

问题描述

下面是表myTable

+++++++++++++++
+ id + myWord +
+++++++++++++++
+  1 + AB123  +
+  2 + A413D  +
+  3 + X5231  +
+  4 + ABE921 +
+++++++++++++++

执行时

SELECT id, Locate('1',myWord) as myPos
FROM myTable;

我的排名为1.

+++++++++++++++
+ id + myPos  +
+++++++++++++++
+  1 + 3      +
+  2 + 3      +
+  3 + 5      +
+  4 + 6      +
+++++++++++++++

我想要实现的是找到整数的第一个位置，这样我将获得下面的输出.

What I want to achieve is finding first position of integer so that I will have below output.

+++++++++++++++++++++++
+ id + myWord + myPos +
+++++++++++++++++++++++
+  1 + AB123  +  3    +
+  2 + A413D  +  2    +
+  3 + X5231  +  2    +
+  4 + ABE921 +  4    +
+++++++++++++++++++++++

有什么想法可以实现吗?

Any Idea how I can achieve this?

推荐答案

借助xdazz答案，我做了一些更改并最终得到了答案...

With help of xdazz answer, I did some changes and got answer finally...

SELECT 
  myWord, 
  LEAST (
    if (Locate('0',myWord) >0,Locate('0',myWord),999),
    if (Locate('1',myWord) >0,Locate('1',myWord),999),
    if (Locate('2',myWord) >0,Locate('2',myWord),999),
    if (Locate('3',myWord) >0,Locate('3',myWord),999),
    if (Locate('4',myWord) >0,Locate('4',myWord),999),
    if (Locate('5',myWord) >0,Locate('5',myWord),999),
    if (Locate('6',myWord) >0,Locate('6',myWord),999),
    if (Locate('7',myWord) >0,Locate('7',myWord),999),
    if (Locate('8',myWord) >0,Locate('8',myWord),999),
    if (Locate('9',myWord) >0,Locate('9',myWord),999)
  ) as myPos
FROM myTable;

这篇关于查找字符串中的数字位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！