Add random string to a field using SQL Update

Here’s a simple script to add a random string to a SQL Update:

update <dbname>
set <field> = (SELECT SUBSTRING(CONVERT(VARCHAR(40), NEWID()),0,9))

This will update each row with a distinct 8 alphanumeric characters, e.g. C2552926

Breaking this down in terms of functionality:

NEWID() – generates a UUID

VARCHAR(40) – tells CONVERT() what string data type, max len 40

CONVERT() – takes the UUID and converts it to a string .

SUBSTRING(...0,9) – takes the value of CONVERT and takes from position 0-9, leaving 8 chars. In this case, we only want the first 8 chars because the 9th is a dash). There’s nothing wrong with adding the dash,

Changing 9 to 50 leaves us this:

11632BF8-EE3C-40CF-9C29-DECC6808C874

If you need to make a complex password in T-SQL, check out this post.

Tags:,

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.