Home » SQL & PL/SQL » SQL & PL/SQL » Filtering results where nth character is numeric
Filtering results where nth character is numeric [message #277784] Wed, 31 October 2007 09:44 Go to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Hi all,

in my SQL WHERE clause I want to filter results based on the field PROJECT_CODE having the first 3 characters = 'INT'
and the 4th character being numeric...

can this be done using SQL?


thank sin advance,
Matt
Re: Filtering results where nth character is numeric [message #277785 is a reply to message #277784] Wed, 31 October 2007 09:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, this can be done and differently depending on version that you should post.

Regards
Michel
Re: Filtering results where nth character is numeric [message #277788 is a reply to message #277784] Wed, 31 October 2007 09:48 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Sorry, that totally slipped my mind.
I am using Oracle 8i

thanks again,
Matt
Re: Filtering results where nth character is numeric [message #277789 is a reply to message #277788] Wed, 31 October 2007 09:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could do:
WHERE subst(field,1,3) = 'INT'
AND substr(field,4,1) in ('0','1','2','3','4','5','6','7','8','9')
Re: Filtering results where nth character is numeric [message #277807 is a reply to message #277784] Wed, 31 October 2007 14:23 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Or another way would be to do

WHERE subst(field,1,3) = 'INT'
AND substr(field,4,1) between '0' and '9'
Re: Filtering results where nth character is numeric [message #277817 is a reply to message #277807] Wed, 31 October 2007 14:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe there is a character set where all figures '0' to '9' are not sequential or where '0' is greater than '9'.

Regards
Michel
Re: Filtering results where nth character is numeric [message #277898 is a reply to message #277817] Thu, 01 November 2007 02:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
or even:
WHERE subst(field,1,3) = 'INT'
AND to_number(substr(field,4,1)) between 0 and 9
Re: Filtering results where nth character is numeric [message #277902 is a reply to message #277898] Thu, 01 November 2007 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case the whole query will fail in error if one row contains a non numeric character in 4th place.

Regards
Michel
Re: Filtering results where nth character is numeric [message #277904 is a reply to message #277902] Thu, 01 November 2007 02:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
True - I was changing Bill B's solutionm, not thinking about the original problem.
Re: Filtering results where nth character is numeric [message #277914 is a reply to message #277784] Thu, 01 November 2007 03:27 Go to previous message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

thank you all for your help.
I used JRowbottom's initial solution and it worked perfectly.

why I didnt think of it, I don't know!
Previous Topic: Dynamic SQL SP
Next Topic: CONCATINATION MULTIPLE COULMN VALUES
Goto Forum:
  


Current Time: Sat Dec 03 03:40:35 CST 2016

Total time taken to generate the page: 0.20740 seconds