Need SQL equivalent of simple Oracle code?

[HiBoo]

I'm trying to convert the following Oracle code to SQL. Could you please help? I've got no documentation on Oracle functions to explain how they work.

substr(BRANCH, -1, 1) || substr(REGISTERNUMBER, -3, 3);

In Oracle, the BRANCH attribute is a VARCHAR(4) and the REGISTERNUMBER is a VARCHAR(6). The SQL attributes are of the same datatype.

I know that the SQL version s/b SUBSTRING(branch,?,?) + SUBSTRING(registernumber,?,?) but I'm not sure of the proper numbers.

Thanks for any input...

 

[karluk]

Trying your substring command on a couple examples it appears that the negative numbers mean "start this many characters before the end of the string". Very interesting. I was not aware that substr had this capability. Of course the next number means "take this many characters from the starting position".

For example with inputs of "HI" and "THERE" your function would yield the last character of 'HI' and the last three characters of 'THERE'.

substr('HI',-1,1)||substr('THERE',-3,3) = 'IERE'

I don't know SQL Server well enough to know whether there's an easy way to make the conversion. Obviously the problem is that with varchar you don't know exactly where the last character of the string is.

 

[Nebuchednezzar]

The negative number in the substring tells it to start at the end of the string. for example, if your value in the branch column was ABCD, then substr(branch, -1, 1) tells it to start to the right of the last value in the branch column (-1), and to get one character from that starting position. Your result would be 'D'.
If you had used substr(branch, -2, 1), then it would start at the right of the second character from the end of the string ('C'), and would get one character. Result would be 'C'.
--essentially, it just says to get the last n characters, where n is your negative number as the second argument to substr. If you use a negative value in the 3rd argument, it will use the 2nd argument as your starting position like before, but it will grab characters moving from right to left rather than from left to right.

To convert to sql code, you can do the following...
for the branch column...
SELECT SUBSTRING(BRANCH,LEN(BRANCH),1) FROM ...
--This takes a substring starting at the left of the last character, and gets one character (you cannot use negative numbers in the substring in sql, so you have to use the len function to find the length.

For the REGISTERNUMBER column...
SELECT SUBSTRING(REGISTERNUMBER,LEN(REGISTERNUMBER)-2,3) FROM ...
--This takes the total length minus 2 (same as counting from the right 3 characters like Oracle does), then takes 3 characters. essentially, it gets the last 3 characters.