Regular Expression equals saving time

The tip I am sharing in this post is very trivial but might save somebody hours of boring manual job. This will come handy especially for people who work in support projects and need to query often at database or application from the information provided in excel sheet.

Problem/Situation:

Your team lead or manager provides you with an excel file having 1000 SR Numbers and you need to verify which of them are not present in your database.

Traditional Solution:

Approach 1:

  1. You prepare a SQL query to find the records in database
  2. You copy one SR number at a time from sheet and query in db.

Approach 2:

  1. You prepare a SQL query to find the records in database
  2. You create a string that can be used by ‘IN’ expression of query for example: where row_id in (‘1-132343’,’1-34343434’,’1-343434’)

Most of the people would go with second approach but the question is how do you create this string (enclosing each one in single quotes (‘) and separated by comma (,)) from excel export.  It can be really boring and tedious if there are 1000’s of SR number in sheet.

Better Solution:

You can use regular expression and have your string ready in a jiffy. For this you will need a software such as TextPad, Edit Plus or PSPad that allows you to use regular expression to search and replace . If you don’t have these software, there is no need to worry. You can use RegExr to the do the same work online. RegExr is an online regular expression checker and tester tool. In this post I will show how you can use RegExr to make your life easier.

1. Open RegExr and click on the replace tab. You will see a screen shown below

image

2. Copy the column SR Column from the excel sheet and paste in the text window in top.
The text in your top and bottom window will look identical at this time.

3. Enter (\d-\d{1,9})\s in the find text field
image

4. Enter ‘$1’, regular expression in the replace text field.
image

That’s it your replace text area is showing you the result which is like
image

Copy this and enclose it in round brackets and use it in your query.

Understanding the expression:

If you have to use this technique effectively you will have to understand the regular expression. I will explain the regular expression that I used to give you an idea. Let’s decipher the match pattern:

Match Pattern:

  • \d : it will match a digit
  • \d{1,9} : a sequence of digit having at least 1 digit and maximum of 9 digits. So, \d-\d{1,9} will match 1-1, 1-11,1-111 till 1-111111111 but not 1-9999999999
  • (\d-\d{1,9}) : () brackets makes the matched string a variable which can be used in the replace pattern
  • \s: New line character

Replace Pattern:

Replace pattern is fairly simple the only thing to understand in it is, significance of $1 as I had mentioned that () makes the matched string a variable.It can accessed in replace pattern by $1. In case if you use two round brackets second variable can be accessed by $2.

If you have any questions or not clear after reading this I wouldn’t blame you. Regular expression are not that easy understand at first but once you get hang of it you will utilize them every where.

Feel free to ask question that you have but I would suggest you to a hands on and learn by hit and trial, that will help you to understand them better. Comments are welcome as always

5 Responses to Regular Expression equals saving time

    • :).. yes you are right .. every decent text editor other than notepad, wordpad and microsoft word has such feature… to allow search and replace using regular expression

  1. Why to go for any tool we already have data given in the Excel sheet and Excel do provides lot of function. For the above example simple solution is use a function =concatenate(“‘”,A1,”‘,”) where A is the column having the SR no. starting with Row 1. You can put this function in any column like column B then copy this function past till the end of the SRs in the column B. You got all the SRs with quotes appended with comma at the end in the column B. Copy this column and use it in the SQL.

    • You are quite right there… actually the main purpose of this post was to show the use of regular expression in everyday problems that we face and I think I choose the wrong problem to do it..Will try to think of another example which will be more relevant to siebel

    • Hello,

      Yes this is for me too. I use a Excel a lot, but not to make excel documents, excel is like screwdriver for me 😀 and concatenate function is my favorite

Leave a Reply

Contribute