Selecting/Retrieving last # of letters

[m0nty005]

Is there a Left or Right method (as in VB) to retrieve only the 1st (or last) # of characters from the Select values?

Example: myTable's myField1 has values of A00ABC, X00RST, & K00MNO. How can I t-sql Select and display the last 4 characters: 0ABC, 0RST, 0MNO ?

Also while I'm at it, what's the t-sql cast/convt for displaying a date of mm/dd/yyyy as mm/dd/yy?

Thanks a bunch!

 

[SQLBill]

I am at home, so I don't have my BOL available. But for the datetime issue, whatever style you use for the four digit year, you drop the 1 and that's the two digit year.

For example:

CONVERT(VARCHAR(10), GETDATE(), 121)

returns the value: 2005-04-07, so then:

CONVERT(VARCHAR(8), GETDATE(), 21)

will return the value: 05-04-07.

As for the other question, will the values in myField1 always be 6 characters? If so, then this will work:

SELECT SUBSTRING(myField1, 3, 6)
FROM myTable

That reads as "return the part of the string, using myField1, starting with the 3rd character and ending at the 6th character."

-SQLBill

Posting advice: FAQ481-4875

 

[iBe]

The number 4 represents the number of character spaces from the edge to include.

SELECT RIGHT(myField1, 4)
FROM myTable

and

CONVERT(varchar(10), myField1, 101) AS fieldName

 

[JamesLean]

Just for further info, SQL Server has the LEFT function, RIGHT function and SUBSTR function for grabbing sections of a value.

To get mm/dd/yy (2 digit year) date format you need to use style 1 in your convert rather than 101 (this will give 4 digit year).

--James