Validate GUID format using SQL

In SQL Server, we have a in-built SQL function called “REPLICATE”. This SQL function is used to validate the number of time’s any specific character appears in a String or varchar. So using REPLICATE function we can validate the GUID format very comfortably. Below is the Query

replicate(‘[0-9A-F]’, 8) + ‘-‘ + replicate(‘[0-9A-F]’, 4) + ‘-‘ +

replicate(‘[0-9A-F]’, 4) + ‘-‘ + replicate(‘[0-9A-F]’, 4) + ‘-‘ +

replicate(‘[0-9A-F]’, 12)

Replicate accepts tow parameters first, set of chars to be validated and second, the number of time the chars should appear. So GUID is nothing but a format in 8-4-4-4-12 (these number here are count of alpha-numeric chars split by hyphen). The above query validates if the given input has GUID format. You can apply this query in columns as shown below

SELECT * FROM   Table WHERE  Column LIKE

replicate(‘[0-9A-F]’, 8) + ‘-‘ + replicate(‘[0-9A-F]’, 4) + ‘-‘ +

replicate(‘[0-9A-F]’, 4) + ‘-‘ + replicate(‘[0-9A-F]’, 4) + ‘-‘ +

replicate(‘[0-9A-F]’, 12)

Similarly you can use this on various data manipulations.

Happy Coding!!!

Please leave your comment or feedback 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s