Tag Archives: procedure

Finding the Character Set For a SQL Field

The other day I was working on a project and I needed to know all of the different types of characters contained in a SQL field so that I could make a regular expression pattern that would not fail. Thus, I decided to write a stored procedure that would loop through a range of characters and test if that character is contained in a specified field in any records. From there I can get a list of all of the used characters and then more intelligently write my regular expression pattern.

This could also be handy if you are trying to cast a field and you keep getting errors because of a character that won’t cast correctly. An example would be trying to cast a varchar or char field into an int and for some reason or another one of the records has a letter in the field. Your cast will fail and it can be tricky to track down where the issue is. With this stored procedure you can quickly see what the offending character is and then query up all the records containing that character.

Example usage:

Feel free to use this if you find is useful, and leave a comment letting me know if it worked well for you.

 

Convert CSV data into a SQL table

Recently I have been working with some report data that is stored statically in a database in CSV format. I had a need to have that CSV data displayed in a tabular format. I searched around and couldn’t find anything written to do this in SQL, so I decided to write a stored procedure to do this very thing. It will take a CSV and parse it out into a SQL table. For instance if I have report data that looks like this…

It would be transformed into this …

FIRSTNAME LASTNAME DOB CHILDREN
John Smith 1/5/1980 Bob,Sally,Joe,Chris
Jane Smith 2/25/1982 Bob,Sally,Joe,Chris
Bruce Wayne 5/13/1975
Peter Parker 5/23/1970

Note that in the last column the commas are preserved because they are within the quotation mark text qualifiers. Below is the stored procedure that does this. Feel free to use it, and leave a comment if you found it useful.