SQL - Reference - The RIGHT and SUBSTRING Functions
One of the drawbacks from pulling information from the WMI is that eventually, you will have a result set which is not very readable to the average person. If you are like me, average people review reports and they would prefer not to have “extra” information which makes a report appear cluttered. Consider the following data set from the fictitious table “Admins”:
Netbios_Name OS_Type Administrator
ACCT324123DAL Windows XP TEST.COM/Corporate/Accounting/Texas/Dallas/Jone3423
ACCT324512DAL Windows XP TEST.COM/Corporate/Accounting/Texas/Dallas/Harb2512
ACCT324887CHI Windows Vista TEST.COM/Corporate/Accounting/Illinois/Chicago/Hern8231
ACCT325123KCM Windows XP TEST.COM/Corporate/Accounting/Missouri/Kansas City/Lidd4321
ACCT323482CHI Windows Vista TEST.COM/Corporate/Accounting/Illinois/Chicago/Bern2034
Earlier in the year, you were asked to “make SMS show us who the administrators are on all boxes,” so you adjusted the MOF and have been happy that your adjustment works! After doing this, you never heard another word until yesterday when someone in upper management wanted a report on the Midwest Accounting Division boxes. You began to do some test reports to see what would be produced and pulled the above data set.
Now, considering you’ve been producing reports for management for a number of years, you know that the record constituting “Administrator” is not going to be accepted as it is quite “messy.” A simple LAN ID is what your superiors are going to require for that particular column. Here is where the RIGHT function will make your life a lot easier.
The syntax for the RIGHT function is as follows:
SELECT RIGHT ([column_name], length)
FROM [table_name]
Luckily, LAN ID’s in your company are uniformly created from the first 4 letters of the user’s last name and the last 4 digits of their employee ID, resulting in an 8-character LAN ID. To pull the last 8 characters from this record, you’d construct a RIGHT statement as follows:
SELECT Netbios_Name , OS_Type , RIGHT(Administrator, 8)
FROM Admins
The result (first line only) from this query resulted in the following:
Netbios_Name OS_Type Administrator
ACCT324123DAL Windows XP one3423
OH NO! Much to your dismay, you have discovered that there is a trailing space at the end of the record. For a basic report, this would not be a problem, as you could just increase the number of characters returned with RIGHT(Administrator, 9). But you know from experience that management is going to also want full user names to match up to the LAN ID’s. That trailing space will problematic later on when you try to JOIN the Admins table to the Users table. This is a situation where the SUBSTRING function can be quite useful.
The syntax for the SUBSTRING function is as follows:
SELECT SUBSTRING ([column_name], starting_position, length)
FROM [table_name]
What you will do in this case is pull the first 8 characters from the right-hand 9. Here is the full SELECT statement using this nested method:
SELECT Netbios_Name , OS_Type , SUBSTRING(RIGHT(Administrators, 9), 1, 8)
FROM Admins
Resulting in the following:
Netbios_Name OS_Type Administrator
ACCT324123DAL Windows XP Jone3423
ACCT324512DAL Windows XP Harb2512
ACCT324887CHI Windows Vista Hern8231
ACCT325123KCM Windows XP Lidd4321
ACCT323482CHI Windows Vista Bern2034
I hope with this information, people will gain some benefit from the SUBSTRING function. Personally, I find it to be very handy at times.