How to Use INSTR With Decode and "No Middle Name"
Structured Query Language (SQL) provides a means for you to set up and manage your databases. You can use the language to query your databases, compare data between data sources and calculate information inside of their tables. Querying a database table for a list of names may present you with a challenge because the names in your list may or may not include middle names. If you run into a problem with parsing string names that contain no middle name data, you can use SQL's INSTR and Decode commands to break down your full names into separate categories for first, last and middle names.
Other People Are Reading
Instructions
-
-
1
Launch your SQL editor then open the database that you want to work with. Select the desired table from the database then type "UPDATE database_master_file SET" into the SQL editor -- omit the quotation marks in the statement. Substitute "database_master_file" with your table's file name -- use underscores instead of spaces to string the file's name together.
-
2
Type "last_name = SUBSTR (record name,1,INSTR(record name,")-1)," to define the first portion of the record as the last name. Omit the statement's quotation marks while preserving the comma at the end -- commas punctuate the end of a field in a SQL statement. Substitute "record name" in the statement with your database's name header.
-
-
3
Add the "first_name = DECODE (TO CHAR(INSTR(pat_name,-,1,2))," statement onto the next line. The "1" and "2" in this statement tell SQL to search for a first name if the name record has one or two spaces.
-
4
Use the following statements to tell the SQL editor to designate the next part of your record's string as the first name. Type "'0', SUBSTR (pat name, INSTR(pat_name,') +1),," "SUBSTR (pat-name, INSTR(pat_name,'')+1," and "INSTR(pat_name,'',1,2) - INSTR(pat_name,") -1))," onto the next three lines with hanging indents. Omit the outer quotation marks in each of the statements and omit the outer comma in the first statement. The commas next to the right-most parentheses are used to punctuate the end of a statement's field.
-
5
Type "pat_middle_name = DECODE (TO_CHAR(INSTR(pat_name,'',1,2)," onto the next line then type "'0', null," onto the next line to tell the SQL editor to omit a middle name field if it doesn't find two spaces in the string. Type "SUBSTR (pat-name, INSTR(pat_name,',1,2)+1)" to tell the SQL editor that the third name in the string is the middle name if it finds two spaces in the record. Omit the quotation marks and preserve the commas in the statements.
-
6
Save your changes then close your SQL editor.
-
1
