SUBSTRING/SUBSTR - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SUBSTRING/SUBSTR

Author

Topic

NAPS
Starting Member

4 Posts

Posted - 2011-11-23 : 11:55:24

Hi,

I am hoping someone could help me with this SQL query (using an Oracle database). I have the following code that returns data ordered by 'ACCOUNTREFERENCE'. I am trying to make it so that the Account reference (code at the end) is returned minus 2 characters

Eg. 12345678 would be returned as 123456

I just can't seem to get the Substr command to work!

I know the normal syntax would be:
SELECT SUBSTR(<column_name>, position, length)

However the code I am trying to apply the command to is at the end of the statement.

Any help would be much appreciated.

Code:

SELECT DISTINCT
    INFOTABLE."PROCESSNO", INFOTABLE."PROCESSNAME", INFOTABLE."PROCSTATUS",
    USRACCOUNT."ACCOUNTREFERENCE"
FROM
    "CM"."INFOTABLE" INFOTABLE,
    "CM"."USRACCOUNT" USRACCOUNT
WHERE
    INFOTABLE."KEYNUMB" = USRACCOUNT."ACCOUNTNO" AND
    (INFOTABLE."PROCESSNAME" = 'PROCESS1' OR
    INFOTABLE."PROCESSNAME" = 'PROCESS2' OR
    INFOTABLE."PROCESSNAME" = 'PROCESS3' OR
    INFOTABLE."PROCESSNAME" = 'PROCESS4' OR
    INFOTABLE."PROCESSNAME" = 'PROCESS5' OR
    INFOTABLE."PROCESSNAME" = 'PROCESS6' OR
    INFOTABLE."PROCESSNAME" = 'PROCESS7' OR
    INFOTABLE."PROCESSNAME" = 'PROCESS8' OR
    INFOTABLE."PROCESSNAME" = 'PROCESS9'OR
    INFOTABLE."PROCESSNAME" = 'PROCESS10'OR
    INFOTABLE."PROCESSNAME" = 'PROCESS11'OR
    INFOTABLE."PROCESSNAME" = 'PROCESS12'OR
    INFOTABLE."PROCESSNAME" = 'PROCESS13'OR
    INFOTABLE."PROCESSNAME" = 'PROCESS14'OR
    INFOTABLE."PROCESSNAME" = 'PROCESS15'OR
    INFOTABLE."PROCESSNAME" = 'PROCESS16'OR
    INFOTABLE."PROCESSNAME" = 'PROCESS17'OR
    INFOTABLE."PROCESSNAME" = 'PROCESS18'OR
    INFOTABLE."PROCESSNAME" = 'PROCESS19'OR
    INFOTABLE."PROCESSNAME" = 'PROCESS20'OR
    INFOTABLE."PROCESSNAME" = 'PROCESS21'OR
    INFOTABLE."PROCESSNAME" = 'PROCESS22') AND
    INFOTABLE."PROCSTATUS" = 'A'
ORDER BY
    USRACCOUNT."ACCOUNTREFERENCE" ASC

"There are no failures, only results".

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-23 : 12:29:13

You'll want to post your question on an Oracle site. SQLTeam.com is for Microsoft SQL Server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-24 : 01:35:50

[code]ORDER BY
SUBSTR(USRACCOUNT."ACCOUNTREFERENCE",1,LENGTH(USRACCOUNT."ACCOUNTREFERENCE")-2) ASC[/code]

Madhivanan

Failing to plan is Planning to fail

NAPS
Starting Member

4 Posts

Posted - 2011-11-25 : 08:38:33

quote:
Originally posted by madhivanan

ORDER BY
SUBSTR(USRACCOUNT."ACCOUNTREFERENCE",1,LENGTH(USRACCOUNT."ACCOUNTREFERENCE")-2) ASC

Madhivanan

Failing to plan is Planning to fail

Thanks for this code Madhivanan, it seems to have improved the situation as I am no longer getting any error messages. However it is not trimming off those last 2 digits for some reason. I wonder if it is possible that due to the security settings of our Oracle database only simple SELECT queries are enabled. I don't think it would make much sense to disable Substrings but that's the only conclusion I have reached at this point.

@tkizer, I'll relocate this thread to an Oracle forum if no one can help. Thanks for the heads up!

"There are no failures, only results".

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-28 : 06:53:22

quote:
Originally posted by NAPS

quote:
Originally posted by madhivanan

ORDER BY
SUBSTR(USRACCOUNT."ACCOUNTREFERENCE",1,LENGTH(USRACCOUNT."ACCOUNTREFERENCE")-2) ASC

Madhivanan

Failing to plan is Planning to fail

Thanks for this code Madhivanan, it seems to have improved the situation as I am no longer getting any error messages. However it is not trimming off those last 2 digits for some reason. I wonder if it is possible that due to the security settings of our Oracle database only simple SELECT queries are enabled. I don't think it would make much sense to disable Substrings but that's the only conclusion I have reached at this point.

@tkizer, I'll relocate this thread to an Oracle forum if no one can help. Thanks for the heads up!

"There are no failures, only results".

Check if there are trialing spaces. The code should work fine

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources