SQL query - 'ON' clause

Question

I have the below query and I need to add another condition on 'ON' clause.
If I need to include another clause for 'ON' which is

i_no = LEFT(replace(right(filename, charindex('\', reverse(filename))-1),'_rprt%[0-9]%.doc', ''),

How do I achieve this?

 SELECT (right(filename, charindex('\', reverse(filename))-1)) phyname,
            replace(filename,(right(filename, charindex('\', reverse(filename))-1)), '') phypath, 
            a_no,
            i_no 
        from 
            #tempimages
        inner join 
            imagestatus 
        on 
            i_no = LEFT(replace(right(filename, charindex('\', reverse(filename))-1),'.doc', ''),
                    LEN(replace(right(filename, charindex('\', reverse(filename))-1),'.doc', ''))-3)

score 1 · Accepted Answer · answered Dec 14 '16 at 10:37

1

If you want to only return records where both of the conditions in your ON clause are true, then use AND:

SELECT  (RIGHT(filename, CHARINDEX('\', REVERSE(filename))-1)) phyname,
        REPLACE(filename,(RIGHT(filename, CHARINDEX('\', REVERSE(filename))-1)), '') phypath, 
        a_no,
        i_no 
FROM #tempimages
INNER JOIN imagestatus ON i_no = LEFT(REPLACE(right(filename, CHARINDEX('\', REVERSE(filename))-1),'.doc', ''),LEN(REPLACE(right(filename, CHARINDEX('\', REVERSE(filename))-1),'.doc', ''))-3)
                     AND  i_no = LEFT(REPLACE(right(filename, CHARINDEX('\', REVERSE(filename))-1),'_rprt%[0-9]%.doc', '')

If records should be returned if either of the conditions are met, then use OR:

SELECT  (RIGHT(filename, CHARINDEX('\', REVERSE(filename))-1)) phyname,
        REPLACE(filename,(RIGHT(filename, CHARINDEX('\', REVERSE(filename))-1)), '') phypath, 
        a_no,
        i_no 
FROM #tempimages
INNER JOIN imagestatus ON i_no = LEFT(REPLACE(right(filename, CHARINDEX('\', REVERSE(filename))-1),'.doc', ''),LEN(REPLACE(right(filename, CHARINDEX('\', REVERSE(filename))-1),'.doc', ''))-3)
                       OR i_no = LEFT(REPLACE(right(filename, CHARINDEX('\', REVERSE(filename))-1),'_rprt%[0-9]%.doc', '')

answered Dec 14 '16 at 10:37

3N1GM4

3,372
3
19
40

thanks, how do I handle if the error raises with invalid length parameter passed to the left or substring function – Jyothi Srinivasa Dec 14 '16 at 11:29
Your function calls are not complete - where you call `LEFT`, your expression terminates before the second parameter (for the number of characters to return) is given. Change to: `LEFT(replace(right(filename, charindex('\', reverse(filename))-1),'_rprt%[0-9]%.doc', ''),2)` and replace the `2` at the end with whatever number of characters you're looking for. – 3N1GM4 Dec 14 '16 at 11:37
filenamepath is = '\\aa\bb\cc\00121.doc' or my filenamepath could be '\\aa\bb\cc\00121_rprt001.doc' So I need to extract filenames for these 2 filename formats(paths) set @rprtdelimeter ='_rprt' right(Left(fileName,CHARINDEX(@rprtdelimeter,fileName)-1) ,charindex('\',reverse(Left(fileName,CHARINDEX(@rprtdelimeter,@fileName)-1) ))-1) – Jyothi Srinivasa Dec 14 '16 at 11:47
This feels like a separate question, rather than something to resolve in the comments - perhaps you should post another question for it. – 3N1GM4 Dec 14 '16 at 11:51
It looks like you're trying to use regex within `REPLACE`, which is not supported - use `PATINDEX` instead, like [this](http://stackoverflow.com/questions/21378193/regex-pattern-inside-sql-replace-function). – 3N1GM4 Dec 14 '16 at 11:53

SQL query - 'ON' clause

1 Answers1