select * from words
where upper('&word') = '&word'
and word <> '&word'
and length(word) <= length('&word')
and
case
when length(word) >= 1
then sign(length('&word') - length(replace('&word', substr(word,1,1))) - length(word) + length(replace(word, substr(word,1,1))))
else 1
end >=0
and
case
when length(word) >= 2
then sign(length('&word') - length(replace('&word', substr(word,2,1))) - length(word) + length(replace(word, substr(word,2,1))))
else 1
end >=0
and
case
when length(word) >= 3
then sign(length('&word') - length(replace('&word', substr(word,3,1))) - length(word) + length(replace(word, substr(word,3,1))))
else 1
end >=0
and
case
when length(word) >= 4
then sign(length('&word') - length(replace('&word', substr(word,4,1))) - length(word) + length(replace(word, substr(word,4,1))))
else 1
end >=0
and
case
when length(word) >= 5
then sign(length('&word') - length(replace('&word', substr(word,5,1))) - length(word) + length(replace(word, substr(word,5,1))))
else 1
end >=0
and
case
when length(word) >= 6
then sign(length('&word') - length(replace('&word', substr(word,6,1))) - length(word) + length(replace(word, substr(word,6,1))))
else 1
end >=0
and
case
when length(word) >= 7
then sign(length('&word') - length(replace('&word', substr(word,7,1))) - length(word) + length(replace(word, substr(word,7,1))))
else 1
end >=0
and
case
when length(word) >= 8
then sign(length('&word') - length(replace('&word', substr(word,8,1))) - length(word) + length(replace(word, substr(word,8,1))))
else 1
end >=0
and
case
when length(word) >= 9
then sign(length('&word') - length(replace('&word', substr(word,9,1))) - length(word) + length(replace(word, substr(word,9,1))))
else 1
end >=0
and
case
when length(word) >= 10
then sign(length('&word') - length(replace('&word', substr(word,10,1))) - length(word) + length(replace(word, substr(word,10,1))))
else 1
end >=0
order by length(word), word;