Using alias name in another column

Question

This question is extended part of my previous question, Finding number position in string.

I have table myTable as below (myWord ==> varchar(10))

++++++++++++
+ myWord   +    
++++++++++++
+ AB123    +
+ A413     +
+ X5231    +
+ ABE921   +
+ 15231    +
+ 523      +
+ ABC      +
++++++++++++

What I wanted is as below.

++++++++++++++++++++++++++++++++
+ myWord   + myPos + NewString +
++++++++++++++++++++++++++++++++
+ AB123    + 3     + AB        +
+ A413     + 2     + A         +
+ X5231    + 2     + X         +
+ ABE921   + 4     + ABE       +
+ 15231    + 1     +           +
+ 523      + 1     +           +
+ ABC      + 999   + ABC       +
++++++++++++++++++++++++++++++++

To get above output, I used below query.

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,
  if (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)
  )=999,myWord,SUBSTR(myWord,1,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)
  )-1)) as NewString
FROM myTable;

My Question Is

After giving name to column as MyPos, why I can't use that name in another column as shown in below query?

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,
  if (myPos=999,myWord,SUBSTR(myWord,1,myPos-1)) as NewString
FROM myTable;

But, this gives me error as

Unknown column 'myPos' in 'field list':

See here for more details

Please suggest me what can be done to make this query shorter by using alias name.

score 2 · Accepted Answer · edited Jun 22 '12 at 13:25

2

Try putting the least part as a subselect. That you can alias.

Something like this:

SELECT 
  mytable.myWord, 
  myPos.l,
  if (l=999,mytable.myWord,SUBSTR(mytable.myWord,1,l-1)) as NewString
FROM myTable, 
(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 l from mytable)
as myPos
where myPos.myword = mytable.myword

SQLFiddle example

edited Jun 22 '12 at 13:25

Fahim Parkar

30,974
45
160
276

answered Jun 22 '12 at 13:07

juergen d

201,996
37
293
362

sql fiddle is showing me many rows & not giving me what is expected – Fahim Parkar Jun 22 '12 at 13:12
2

great & thanks a ton... I also did some RND. See [here](http://sqlfiddle.com/#!2/ea7e3/6) – Fahim Parkar Jun 22 '12 at 13:23
Any inputs on [this question](http://stackoverflow.com/questions/11155690/use-of-regex-in-mysql) – Fahim Parkar Jun 22 '12 at 13:28

score 1 · Answer 2 · answered Jun 22 '12 at 13:27

Based on @juergend answer, I also tried with below and its working too.

SELECT 
  t1.myWord, t2.l as MyPos, if (t2.l=999,t1.myWord,SUBSTR(t1.myWord,1,t2.l-1)) as NewString
FROM myTable t1 JOIN  
(select id, 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 l from mytable) as t2
 ON t1.id=t2.id;

NOTE : For this I have added id in table to JOIN two tables.

Using alias name in another column

My Question Is

See here for more details

2 Answers2

Demo

Linked