MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for June, 2010

Oracle 11g XE Delay?

with one comment

Somebody posted a comment inquiring about the release of Oracle 11g XE. They felt it had been delayed. As far as I know, the last word on that came from Andy Mendelsohn last October, as published in this InfoWorld article.

The Oracle Database 11g XE is released and the download link is here.

When Andy Mendelsohn originally announced Oracle 11g XE in an interview with the NY Times, he clearly said it would be based on the terminal release of Oracle 11g. I also suspect it’ll include APEX 4.0, which was recently released. Maybe we’ll see the release at or near Oracle Open World 2010.

I don’t endorse the negative remarks in the InfoWorld article about why Oracle 11g XE isn’t released. While I have no direct knowledge of the forthcoming release, I think there’s an alternative explanation for any delay. Based on my involvement with the Oracle 10g XE release, I believe Oracle wants a rock solid starter version. At least, that was a major concern when they released Oracle 10g XE.

Written by maclochlainn

June 28th, 2010 at 1:42 pm

Correlated Update Statement

without comments

My students wanted some additional examples on correlated update statements. I’ve been struggling with what the right way may be to illustrate them.

Correlated subqueries are a hard concept to explain to those new to SQL. While correlated update statements seem impossibly obscure to many or inordinately complex. New SQL developers often flee to the comfort of procedural programs when it comes to update statements.

This uses my video store data model. It’s a correlated update statement to clean up potential corrupt data. More or less something a DBA might run to ensure a business rule hasn’t been violated over time. It checks for the correct foreign key value in a table when a dependent table contains one or more than one row of data.

The aqua-green box highlights a subquery that aggregates foreign key columns and groups the result with the foreign key value. The results from this subquery become a run-time view or derived table. The result set is a foreign key value and a substitute string literal value for each row in the contact table. These results correlate to the update statement’s rows based on the input parameter. The input parameter is a column from each updated row.

A unique key (or check constraint) exists on the combination of the common_lookup_table, common_lookup_column, and common_lookup_type columns. This ensures that only one row is returned and assigned to the member_type column in the member table. The update statement naturally works in either Oracle or MySQL without any porting changes.

While this type of solution is powerful in its own right, I thought it might be interesting to see their procedural equivalents. These correlated subqueries run for each row returned by the master query (or outermost statement). Therefore, they act like functions.

Procedural equivalents (or user-defined functions) simplify the update statement like so:

UPDATE member m
SET    member_type = get_member_type(m.member_id);

If you’re interested in seeing how you would implement this solution in a user-defined function, just expand the dropdown that interest you.

You can query the results of the update statement with the following.

As always, I look forward to helping and gaining insight.

Written by maclochlainn

June 27th, 2010 at 10:12 pm

Debugging MySQL Functions

with 3 comments

Somebody, who read this post on Debugging MySQL Procedures, asked why the strategy of selecting a string literal didn’t work in a MySQL function. That’s easy, they’re not designed to support a SELECT statement, only a SELECT-INTO statement.

Why? That’s the purpose of a function to perform something and return a single reply.

That’s also why a MySQL functions only support the IN mode of operation for formal and call parameters. When formal parameters are restricted to in-mode-only operations, they implement a pass-by-value function model. This can also be expressed from the other side of the looking glass. In that case, MySQL functions don’t support pass-by-reference functions that use the INOUT or OUT mode operations.

If you put a SELECT statement in a function to print internal values or comments, it raises an error. Take for example the following attempt to create the debugging function with an echo of output (that works in stored procedures).

CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT '[Debug #1]';
  RETURN 1;
END;
$$

It fails to create the function because you’ve violated a key integrity rule. It also raises the following error:

ERROR 1415 (0A000): Not allowed to return a result set from a function

You have two potential solutions to this problem. The first is limited and inflexible. The second isn’t as limited or inflexible and is the recommended way to debug your functions without a tool. That’s to use a temporary table to record run-time debugging events.

Written by maclochlainn

June 26th, 2010 at 6:51 pm

Show indexes in Oracle

with 2 comments

One of my students asked how you could show index from table_name; in Oracle. They were chagrined when I told them there wasn’t an equivalent command. Outside of using Quest’s Toad or Oracle SQL*Developer, you can query the data catalog, like so:

-- SQL*Plus formatting commands.
COLUMN index_name FORMAT A32
COLUMN column_position FORMAT 999 HEADING "COLUMN|POSITION"
COLUMN column_name FORMAT A32
 
-- Ordinary query with a substitution variable.
SELECT i.index_name
,      ic.column_position
,      ic.column_name
FROM   user_indexes i JOIN user_ind_columns ic
ON     i.index_name = ic.index_name
WHERE  i.TABLE_NAME = UPPER('&input')

Naturally, this is a subset of what’s returned by the show index from table_name; syntax. There is much more information in these tables but I only wanted to show an example.

The UPPER function command ensures that the table name is found in the database. Unless you’ve created a case sensitive object, they’re stored in uppercase strings.

While a single SQL statement works well, a little organization in PL/SQL makes it more readable. A display_indexes function provides that organization. It only displays normal indexes, not LOB indexes, and it depends on a schema-level collection of strings. This is the user-defined type (UDT) that I used for the collection.

CREATE OR REPLACE TYPE index_table AS TABLE OF VARCHAR2(200);
/

The following is the definition of the function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
CREATE OR REPLACE FUNCTION display_indexes
( pv_table_name VARCHAR2 ) RETURN INDEX_TABLE IS
 
  -- Declare an iterator for the collection return variable.
  index_counter  NUMBER := 1;
  column_counter NUMBER;
 
  -- Declare and initialize local collection variable as return type.
  index_desc INDEX_TABLE := index_table();
 
  -- Get indexes.
  CURSOR index_name (cv_table_name VARCHAR2) IS
    SELECT   i.index_name
    FROM     user_indexes i
    WHERE    i.TABLE_NAME = cv_table_name
    AND      i.index_type = 'NORMAL'
    ORDER BY 1;
 
  -- Get index columns.    
  CURSOR index_columns (cv_index_name VARCHAR2) IS
    SELECT   ic.column_position
    ,        ic.column_name
    FROM     user_ind_columns ic
    WHERE    ic.index_name = cv_index_name
    ORDER BY 1;
 
BEGIN
 
  -- Assign the table name to the collection.
  index_desc.EXTEND;
  index_desc(index_counter) := UPPER(pv_table_name);
  index_counter := index_counter + 1;
 
  FOR i IN index_name(UPPER(pv_table_name)) LOOP
 
    -- Assign the index name to the collection.
    index_desc.EXTEND;
    index_desc(index_counter) := LPAD(i.index_name,2 + LENGTH(i.index_name),' ');
 
    -- Set column counter on entry to nested loop.
    column_counter := 1;
 
    FOR j IN index_columns(i.index_name) LOOP
 
      IF column_counter = 1 THEN
 
        -- Increment the column counter, extend space, and concatenate to string.
        column_counter := column_counter + 1;
        index_desc.EXTEND;
        index_desc(index_counter) := index_desc(index_counter) || '(' || LOWER(j.column_name);
 
      ELSE
 
        -- Add a subsequent column to the list.
        index_desc(index_counter) := index_desc(index_counter) || ',' || LOWER(j.column_name);
 
      END IF;
 
    END LOOP;
 
    -- Append a close parenthesis and incredment index counter.
    index_desc(index_counter) := index_desc(index_counter) || ')';
    index_counter := index_counter + 1;
 
  END LOOP;
 
  -- Return the array.
  RETURN index_desc;
END;
/

You can call the function with this syntax:

SELECT column_value AS "TRANSACTION INDEXES"
FROM   TABLE(display_indexes('TRANSACTION'));

It returns the following formatted output for the TRANSACTION table, which is much nicer than the SQL output. Unfortunately, it will take more effort to place it on par with the show index from table_name; in MySQL.

TRANSACTION INDEXES
------------------------------------------------------------------------------------------------------------------------------
TRANSACTION
  PK_TRANSACTION(transaction_id)
  UQ_TRANSACTION(rental_id,transaction_type,transaction_date,payment_method_type,payment_account_number,transaction_account)

As always, I hope it helps folks.

Written by maclochlainn

June 22nd, 2010 at 1:28 am

MySQL Timestamp to Date?

with 3 comments

One of my ex-students asked for an example of converting a DATETIME column into a DATE data type in MySQL. He’d tried a few approaches and hadn’t been successful. It’s best to use the DATE function to convert a DATETIME to a DATE in MySQL.

  1. Create a sample TIMECLOCK table.
DROP TABLE IF EXISTS timeclock;
 
CREATE TABLE TIMECLOCK
( timeclock_id  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, timein        datetime
, timeout       datetime);
  1. Insert two rows with values in the TIMEIN column.
-- Insert two rows with automatic numbering and only a "time in" value.
INSERT INTO timeclock
( timein )
VALUES
(NOW()),(NOW());
  1. Update the previously inserted rows with values in the TIMEOUT column. The first UPDATE statement inserts a UTC date into the DATETIME column. That date is a time stamp of of the next day at 12:00 A.M. in the morning. The second UPDATE statement updates the TIMEOUT column with a current time stamp plus 4 hours, thirty-two minutes, and thirty-three seconds. Then, the code segment queries the results.
-- Update with tomorrow's future date at 12:00 A.M..
UPDATE timeclock
SET    timeout = ADDDATE(DATE(NOW()), INTERVAL 1 DAY)
WHERE  timeclock_id = 1;
 
-- Update with a timestamp 4 hours, thirty-two minutes, and thirty-three seconds in the future.
UPDATE timeclock
SET    timeout = ADDTIME(NOW(), '4:32:33')
WHERE  timeclock_id = 2;
 
-- Query the value sets.
SELECT timein, timeout FROM timeclock;

This returns:

+---------------------+---------------------+
| timein              | timeout             |
+---------------------+---------------------+
| 2010-06-18 16:16:08 | 2010-06-19 00:00:00 |
| 2010-06-18 16:16:08 | 2010-06-18 20:48:42 |
+---------------------+---------------------+
2 rows in set (0.00 sec)
  1. Query the differences of the timestamps as dates and times. The DATE function lets you convert a DATETIME into a DATE data type. Then, the DATEDIFF calculates the difference and returns an integer result (the interval of days). You calculate the time difference by using the TIMEDIFF function.
-- Query the difference in intervals of days.
SELECT DATE(timeout) AS dateout
,      DATE(timein) AS datein
,      DATEDIFF(DATE(timeout),DATE(timein))
FROM timeclock;
 
-- Query the difference in intervals of time.
SELECT timeout
,      timein
,      TIMEDIFF(timeout,timein)
FROM   timeclock;

These return:

+------------+------------+--------------------------------------+
| dateout    | datein     | DATEDIFF(DATE(timeout),DATE(timein)) |
+------------+------------+--------------------------------------+
| 2010-06-19 | 2010-06-18 |                                    1 |
| 2010-06-18 | 2010-06-18 |                                    0 |
+------------+------------+--------------------------------------+
2 rows in set (0.00 sec)
 
+---------------------+---------------------+--------------------------+
| timeout             | timein              | TIMEDIFF(timeout,timein) |
+---------------------+---------------------+--------------------------+
| 2010-06-19 00:00:00 | 2010-06-18 16:16:08 | 07:43:52                 |
| 2010-06-18 20:48:42 | 2010-06-18 16:16:08 | 04:32:34                 |
+---------------------+---------------------+--------------------------+
2 rows in set (0.00 sec)

You can also use:

SELECT CAST(timein AS DATE)
,      CAST(timeout AS DATE)
FROM   timeclock;

Hope this helps the one who asked how and anybody else who runs across it.

Written by maclochlainn

June 18th, 2010 at 5:48 pm

Posted in MySQL,sql

A \G Option for Oracle?

with 7 comments

The \G option in MySQL lets you display rows of data as sets with the columns on the left and the data on the write. I figured it would be fun to write those for Oracle when somebody pointed out that they weren’t out there in cyberspace (first page of a Google search ;-)).

I started the program with a student’s code. I thought it a bit advanced for the student but didn’t check if he’d snagged it somewhere. Thanks to Niall Litchfield, I now know that the base code came from an earlier post of Tom Kyte. Tom’s example code failed when returning a Blob, BFile, or CFile column.

Naturally, there are two ways to write this. One is a procedure and the other is the function. This post contains both. The procedure is limited because of potential buffer overflows associated with the DBMS_OUTPUT package’s display. A function isn’t limited because you can return a collection from the function.

As usual, I hope this helps folks.

Written by maclochlainn

June 14th, 2010 at 1:01 am

Posted in MySQL,Oracle,Oracle XE,pl/sql,sql

Tagged with ,

Goodbye to my iPhone

with 4 comments

For almost two years, I’ve loved my iPhone. That’s amazing, since I live where AT&T has never provided 3G coverage. The contract expires this summer.

Admittedly, business trips were refreshing because I generally always had a 3G connection. That is, until the last six months or so. During that period of time, I’ve been experiencing some random dropped calls. For example, AT&T dropped my 3G connections about 8 times in less than 30 minutes in April. All three calls went over 5 minutes before they were dropped, and they were made from a hotel in Anaheim, California (near Angels Stadium). Those drops certainly made it appear that their system couldn’t handle the volume of concurrent calls, but it could have been something else. ;-)

The final straw was AT&T’s unlimited data plan change announcement. Wow! It caught me by surprise. Their back peddling with a subsequent announcement of qualified renewed contracts doesn’t work for me. They’ve lost what little trust I had for them.

Fortunately, I’d deferred buying an iPad. I’d planned to order an iPad 3G model this last week, but now it will definitely be a Wi-Fi only device! I’m also deferring the purchase now to see if there’s another surprise in the wings.

When I compare the cost of my iPhone against the HTC EVO, the iPhone loses. I found Daniel Lyon’s article Phone Fight! interesting for many reasons. While his comparison of Microsoft versus Apple of yesteryear to Google versus Apple of today is interesting, this quote strikes me:

“Right now the most advanced smart phone on the market is arguably not the new iPhone 4 but rather an Android device called the EVO 4G, made by HTC, a Taiwanese company. That phone has front and rear cameras, and shoots hi-def video, just like the new iPhone 4. But the EVO has a bigger display, its camera shoots in higher resolution, and it can turn itself into a mobile Wi-Fi hotspot, something the iPhone 4 can’t do.”

The mobile Wi-Fi hotspot means my iPad Wi-Fi works virtually anywhere. That alone is probably the single most important reason why I considered dropping my iPhone. Alas, I was disabused of the notion today by David Pogue (6/17/2010). The battery life is pathetic.

Let’s face it: the iPhone interface is AWESOME! Therefore, my idea of replacing my iPhone with an EVO will be a nearly forgotten memory, like my HP-25C of college days and Star Wars IV premiere gone by …

Written by maclochlainn

June 13th, 2010 at 12:55 am

Posted in EVO,iPhone

A couple DBMS_SQL limits

without comments

While developing a dynamic SQL example in Oracle 11g that builds a query based on available display columns, I found two interesting error messages. Now instead of noting it for the umpteenth time, I’m documenting it for everybody. The error messages are generated when this DBMS_SQL package’s statement is a SELECT statement, and is executed with either a BLOB, BFILE or CFILE column in the list of returned columns.

26
STATUS := dbms_sql.EXECUTE(stmt);

BLOB data type

You get the following error when a column in the query has a BLOB data type. If you alter the query to exclude the column, no error occurs.

BEGIN test('DEMO'); END;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
ORA-06512: at "SYS.DBMS_SQL", line 1575
ORA-06512: at "STUDENT.TEST", line 26
ORA-06512: at line 1

BFILE or CFILE data type

You get the following error when a column in the query has a BFILE or CFILE data type. If you alter the query to exclude the column, no error occurs.

BEGIN test('DEMO'); END;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got FILE
ORA-06512: at "SYS.DBMS_SQL", line 1575
ORA-06512: at "STUDENT.TEST", line 26
ORA-06512: at line 1

It’s never a joy to debug the DBMS_SQL package, at least it’s never a joy for me. I hope this helps somebody sort out an issue more quickly.

Written by maclochlainn

June 12th, 2010 at 11:43 pm

VMWare Fusion Permissions

with 3 comments

It’s always interesting when I have to sort out problems with VMWare Fusion on my Mac OS X. Right, as you guessed, interesting means frustrating. ;-) What started the whole thing was my investigating why VMWare networking would sometimes not start. I noticed the problem began after my upgrade to VMWare Fusion 3.1.0 (261058).

Rather than reboot the Mac OS X, which has fixed the problem, I tried to restart the service after closing my VMs. You can find how to do that in this older post of mine.

When I tried to restart it with the following command:

# sudo /Library/Application\ Support/VMware\ Fusion/boot.sh  --restart

I got the following error on VMWare file permissions:

VMware Fusion 261058: Shutting down VMware Fusion: 
Stopped DHCP service on vmnet1
Disabled hostonly virtual adapter on vmnet1
Stopped DHCP service on vmnet8
Stopped NAT service on vmnet8
Disabled hostonly virtual adapter on vmnet8
Stopped all configured services on all networks
No matching processes were found
No matching processes were found
No matching processes were found
No matching processes were found
No matching processes were found
No matching processes were found
(kernel) Kext com.vmware.kext.vmcrosstalk not found for unload request.
Failed to unload com.vmware.kext.vmcrosstalk - (libkern/kext) not found.
(kernel) Kext com.vmware.kext.vmmon not found for unload request.
Failed to unload com.vmware.kext.vmmon - (libkern/kext) not found.
 
VMware Fusion 261058: Starting VMware Fusion: 
2010-06-10 22:22:30.588 repair_packages[455:607] PackageKit: *** Missing bundle identifier: /Library/Receipts/vpn.pkg
Verifying files from package 'com.vmware.fusion.application' on '/'.
	Permissions differ on "Library/Application Support/VMware Fusion/VMDKMounter.app/Contents/MacOS/vmware-vmdkMounter", should be -rwxr-xr-x , they are -rwsr-xr-x .
	Warning: SUID file 'Library/Application Support/VMware Fusion/VMDKMounter.app/Contents/MacOS/vmware-vmdkMounter' has been modified and will not be repaired.
Finished verifying files from package 'com.vmware.fusion.application' on '/'.
Started network services
Verifying and re-installing files from /Library/Application Support/VMware Fusion/thnuclnt

Navigating to the directory, an ls -al found the two files below and their respective permissions.

drwxr-xr-x  4 root  wheel      136 Jun 10 22:51 .
drwxr-xr-x  5 root  wheel      170 May 27 21:22 ..
-rwsr-xr-x  1 root  wheel  1593620 May 21 03:51 vmware-vmdkMounter
-rwsr-xr-x  1 root  wheel  1475396 May 21 03:51 vmware-vmdkMounterTool

I thought perhaps both files required the same permissions but I was wrong. If you change the permissions on the vmware-vmdkMounterTool file, you’ll raise an error telling you that it should be -rwsr-xr-x. If you make that same mistake too, I’ve got the reset syntax at the bottom of the post.

You should only change the file permissions of vmware-vmdkMounter file. The following syntax lets you remove the sticky bit from the user permissions but you’ll need the root password (the administrator password).

sudo chmod u=rwx,go=rx vmware-vmdkMounter

That should leave you with the following permissions:

drwxr-xr-x  4 root  wheel      136 Jun 10 22:51 .
drwxr-xr-x  5 root  wheel      170 May 27 21:22 ..
-rwxr-xr-x  1 root  wheel  1593620 May 21 03:51 vmware-vmdkMounter
-rwsr-xr-x  1 root  wheel  1475396 May 21 03:51 vmware-vmdkMounterTool

When you restart you should get the following pseudo clean output. Pseudo because apparently the two errors are not meaningful. At least, I couldn’t find anything on them and VMWare Fusion now works. I’ll probably investigate this a bit more later, and I’ll update anything in this post. If you know something, post it as a comment to help everybody.

VMware Fusion 261058: Shutting down VMware Fusion: 
Stopped DHCP service on vmnet1
Disabled hostonly virtual adapter on vmnet1
Stopped DHCP service on vmnet8
Stopped NAT service on vmnet8
Disabled hostonly virtual adapter on vmnet8
Stopped all configured services on all networks
No matching processes were found
No matching processes were found
No matching processes were found
No matching processes were found
No matching processes were found
No matching processes were found
(kernel) Kext com.vmware.kext.vmcrosstalk not found for unload request.
Failed to unload com.vmware.kext.vmcrosstalk - (libkern/kext) not found.
(kernel) Kext com.vmware.kext.vmmon not found for unload request.
Failed to unload com.vmware.kext.vmmon - (libkern/kext) not found.
 
VMware Fusion 261058: Starting VMware Fusion: 
2010-06-10 22:58:45.276 repair_packages[861:607] PackageKit: *** Missing bundle identifier: /Library/Receipts/vpn.pkg
Verifying files from package 'com.vmware.fusion.application' on '/'.
Finished verifying files from package 'com.vmware.fusion.application' on '/'.
Started network services
Verifying and re-installing files from /Library/Application Support/VMware Fusion/thnuclnt

If you fat fingered the resetting command and also changed the vmware-vmdkMounterTool file permissions, you can reset them to shared user by using the following syntax:

sudo chmod u=rwxs,go=rx vmware-vmdkMounterTool

As always, I hope this helps others.

Written by maclochlainn

June 10th, 2010 at 11:28 pm

LIKE operator in Excel

with 2 comments

While discussing the limitations of wild card comparisons in the Microsoft Excel’s IF function, I resolved to share my ISLIKE function. It acts like the LIKE operator in SQL. You can’t use the word like as the function name because it’s a reserved word in Visual Basic for Applications (VBA).

The function lets you compare any string to see if it is contained in another string. The default mode is case-insensitive, and the override mode case-sensitive. The optional third element lets you override the default comparison method.

You should remember or note that a number 0 is always evaluated as false, and any other number (negative or positive) is always true. You can put the ISLIKE function in a macro sheet or an Add-in library. I recently posted instructions on how to create your own Add-in library.

Here is the VBA code for the user defined ISLIKE function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Function ISLIKE( inValue As Variant _
               , hasValue As Variant _
               , Optional caseSensitive As Integer = 0)
 
  ' Define a logical return variable and assign a default value.
  Dim logicReturn As Boolean
  logicReturn = False
 
  If caseSensitive Then
 
    ' Finds a case-insensitive value as a component in a larger value.
    If inValue Like "*" + hasValue + "*" Then
      logicReturn = True
    End If
 
  Else
 
    ' Finds a case-sensitive value as a component in a larger value.
    If UCase(inValue) Like "*" + UCase(hasValue) + "*" Then
      logicReturn = True
    End If
 
  End If
  ' Return the logical value.
  ISLIKE = logicReturn
 
End Function

Line numbers 12 and 19 use the "*", which is equivalent to the regular expression of "\.*". I’ve no idea why they made this choice, and it’s not well explained on Microsoft’s web site covering SmartTags.

You can shorten the program and make it slightly less efficient by performing the case sensitive match twice. The following doesn’t rely on nested block IF statements. You also must change the third parameter to a Boolean or the Not operator won’t work correctly.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Function ISLIKE( lookupValue As Variant _
               , containsValue As Variant _
               , Optional caseSensitive As Boolean = 0)
 
  ' Define a logical return variable and assign a default value.
  Dim logicReturn As Boolean
  logicReturn = False
 
  ' Finds a case-insensitive value as a component in a larger value.
  If caseSensitive And _
     lookupValue Like "*" + containsValue + "*" Then
      logicReturn = True
 
  ' Finds a case-sensitive value as a component in a larger value.
  ElseIf Not caseSensitive And _
    UCase(lookupValue) Like "*" + UCase(containsValue) + "*" Then
      logicReturn = True
 
  End If
 
  ' Return the logical value.
  ISLIKE = logicReturn
 
End Function

Somebody asked me, “How this can be useful?” That meant from their perspective, how is it useful beyond a comparison of cell values. A quick example of that additional utility can be demonstrated from an early post. In that post on replacing a VLOOKUP function with a UDF, there’s compound logic used to find the occurrence of a substring in a string. The ISLIKE function can simplify that logic.

Here’s that snippet of the code from that referenced post. The snippet evaluates whether the base string is greater than one character and checks the second character of the string to see if it is a - (minus sign).

58
ElseIf Len(LetterGrades(i)) > 1 And Mid(LetterGrades(i), 2, 1) = "-" Then

You can replace the compound logic with a call to the LIKE function. It would replace line 58 as follows:

58
ElseIf ISLIKE(LetterGrades(i),"-") Then

The logic of the function examines to see if the first parameter contains the second parameter. This is what is meant by like. In the following illustration, you examine if a string contains a substring (or is like a substring). The cell A2 is also assigned a Range name. Range names can increase the readability of your models and functions. All but the last one perform case insensitive comparisons that find whether the substring in cell A2 is found with cell range of the B5:B10. The B10 formula performs a case sensitive match.

Another logic comparison uses grades to see if a minus sign is found in them. The example compares the cell values in A1 and A2 against the B1 and B2 values. Returning true for one and false for the other cases in column C of the respective rows.

Here are the calls to the ISLIKE user defined function. The first one is case-insensitive and the second case-sensitive, but there’s no case sensitivity with a minus sign, -.

Spreadsheet Functions
Cell Formula
C1 =ISLIKE(A1,B1)
C2 =ISLIKE(A2,B2,TRUE)

As always, I hope this helps. If you’ve comments or alternative approaches, please let me know.

Written by maclochlainn

June 4th, 2010 at 11:28 am