3

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.

Improve this question

2 Answers 2

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

sql fiddle is showing me many rows & not giving me what is expected
great & thanks a ton... I also did some RND. See here
Any inputs on this question
1

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.

Demo

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.