By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,265 Members | 1,333 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,265 IT Pros & Developers. It's quick & easy.

SUBSTRING in where clause

maxamis4
Expert 100+
P: 295
Hello folks,

I have the following substring statement I am using but I want to filter by my substring, can anyone help


Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT     last_name, SUBSTRING(alternate_identifier, 5, 2) AS Contractor
  3. FROM         ca_contact
  4. WHERE     (contact_type = 2308) AND (inactive <> 1)
  5.  
  6.  

if i try this it fails with parsing errors


Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT     last_name, SUBSTRING(alternate_identifier, 5, 2) AS Contractor
  3. FROM         ca_contact
  4. WHERE     (contact_type = 2308) AND (inactive <> 1) AND (Contractor = 'DC')
  5.  
  6.  
thanks!
Dec 2 '09 #1

✓ answered by maxamis4

I found it thank you all for looking into this. Here is the what I found. The alias unlike oracle can not be referenced in the where clause. So to perform the calculation you must reference the table.[field]name followed by the formula. See below for example:

Expand|Select|Wrap|Line Numbers
  1. SELECT     last_name, SUBSTRING(alternate_identifier, 5, 2) AS Contractor
  2. FROM         ca_contact
  3. WHERE     (SUBSTRING(alternate_identifier, 5, 2) = 'DC')
  4.  

Share this Question
Share on Google+
1 Reply


maxamis4
Expert 100+
P: 295
I found it thank you all for looking into this. Here is the what I found. The alias unlike oracle can not be referenced in the where clause. So to perform the calculation you must reference the table.[field]name followed by the formula. See below for example:

Expand|Select|Wrap|Line Numbers
  1. SELECT     last_name, SUBSTRING(alternate_identifier, 5, 2) AS Contractor
  2. FROM         ca_contact
  3. WHERE     (SUBSTRING(alternate_identifier, 5, 2) = 'DC')
  4.  
Dec 2 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.