2

Does anyone have any SQL code to automatically generate words (all letter, no number).

Is there any inline SQL to perform this on a column?

We are trying to obfuscate first/last name and other word columns in table.

This answer currently does not work on column, because it generates the same result within a column.

select top (@stringlength) char(abs(checksum(newid())) % 26 + ascii('A'))
from sys.all_objects
for xml path('')

I tried this, but functions are slow, inline queries can be faster, feel free to improve or rewrite below

CREATE VIEW [dbo].[RandomValueVw]
AS
SELECT RAND() as random_value


CREATE function dbo.RandomWordGenerate(@length int)
returns varchar(50)
as begin

    DECLARE 
        @text nvarchar(255),
        @i int

    SET @i = 0
    SET @text = ''

    WHILE (@i < @length)
    BEGIN
        SET @text = @text + CHAR((select * from [dbo].[RandomValueVw]) * 26 + 65)
        SET @i = @i + 1
    END

    return @text

end
6

CRYPT_GEN_RANDOM is your friend for this kind of thing. It creates a simple number of the length you desire, and is about as lightweight as you can get for a cryptographic strength random number. RAND(), in contrast will always generate the same sequence of numbers when called with the same seed value, and as such is definitely not cryptographically secure.

Anyway, building from your prior question, we'll add a [First Name] column and a [Last Name] column, as in:

USE tempdb;
GO
DROP TABLE IF EXISTS dbo.MyTable;
CREATE TABLE dbo.MyTable
(
    MyTableID int NOT NULL
        CONSTRAINT PK_MyTable
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , [Date of Birth] date NOT NULL
    , [First Name] nvarchar(100) NOT NULL
    , [Last Name] nvarchar(100) NOT NULL
);
GO

Here, we'll insert a couple of test rows:

INSERT INTO dbo.MyTable ([Date of Birth], [First Name], [Last Name])
VALUES ('1990-01-01', 'Max', 'Vernon')
    , ('1989-01-01', 'Apple', 'Book');

SELECT *
FROM dbo.MyTable;

The results:

+-----------+---------------+------------+-----------+
| MyTableID | Date of Birth | First Name | Last Name |
+-----------+---------------+------------+-----------+
|         1 | 1990-01-01    | Max        | Vernon    |
|         2 | 1989-01-01    | Apple      | Book      |
+-----------+---------------+------------+-----------+

Now, we'll run the update statement against the table:

DECLARE @MinAge int;
DECLARE @MaxAge int;

SET @MinAge = 10;
SET @MaxAge = 20;
UPDATE dbo.MyTable
SET [Date of Birth] = DATEADD(DAY
    , (1 - (CONVERT(int, CRYPT_GEN_RANDOM(2)) % ((@MaxAge - @MinAge) * 365)))
    , CONVERT(date, DATEADD(YEAR, 1 - @MinAge, GETDATE()))
    )
    , [First Name] = CONVERT(nvarchar(100), CRYPT_GEN_RANDOM(10))
    , [Last Name] = CONVERT(nvarchar(100), CRYPT_GEN_RANDOM(10));

Now, if we look at the table contents, we see:

SELECT *
FROM dbo.MyTable;
+-----------+---------------+------------+-----------+
| MyTableID | Date of Birth | First Name | Last Name |
+-----------+---------------+------------+-----------+
|         1 | 2003-07-22    | ⢢蝤偕龿ື     | 槈ស㎫答덌  |
|         2 | 2008-09-13    | ❈敍봻坄   | ꈨ氕㽲박ે    |
+-----------+---------------+------------+-----------+

As you can see, all data has been quite drastically changed.

If you're not quite happy with the random characters generated in the above example, you can generate less confusion values using this replacement for the [First Name] and [Last Name] bits:

    , [First Name] = REPLICATE(CHAR(CONVERT(int, (CRYPT_GEN_RANDOM(1) % 26) + 65)), 10)
    , [Last Name] = REPLICATE(CHAR(CONVERT(int, (CRYPT_GEN_RANDOM(1) % 26) + 65)), 10);

This results in the following output:

+-----------+---------------+------------+------------+
| MyTableID | Date of Birth | First Name | Last Name  |
+-----------+---------------+------------+------------+
|         1 | 2005-08-13    | PPPPPPPPPP | DDDDDDDDDD |
|         2 | 2002-08-12    | QQQQQQQQQQ | LLLLLLLLLL |
+-----------+---------------+------------+------------+

If you really need human-readable words, you could use a word list to randomly replace the values with actual words. I've used a small portion1 of the BIP-0039 English language wordlist in the example below; however you could easily use any suitable list of words.

DROP TABLE IF EXISTS dbo.WordList;

CREATE TABLE dbo.WordList
(
    Word varchar(20) NOT NULL
        CONSTRAINT PK_WordList
        PRIMARY KEY CLUSTERED
    , OrderBy AS (CONVERT(int, CRYPT_GEN_RANDOM(2)))
) ON [PRIMARY];

INSERT INTO dbo.WordList
VALUES ('abandon')
, ('ability')
, ('able')
, ('about')
, ('above')
, ('absent')
, ('absorb')
, ('abstract')
, ('absurd')
, ('abuse')
, ('access')
, ('accident')
, ('account')
, ('accuse')
, ('achieve')
, ('acid')
, ('acoustic')
, ('acquire')
, ('across')
, ('act')
, ('action')
, ('actor')
, ('actress')
, ('actual')
, ('adapt')
, ('add')
, ('addict')
, ('address')
, ('adjust')
, ('admit')
, ('adult')
, ('advance')
, ('advice')
, ('aerobic')
, ('affair')
, ('afford')
, ('afraid')
, ('again')
, ('age')
, ('agent')
, ('agree')
, ('ahead')
, ('aim')
, ('air')
, ('airport')
, ('aisle')
, ('alarm')
, ('album')
, ('alcohol')
, ('alert')
, ('alien')
, ('all')
, ('alley')
, ('allow')
, ('almost')
, ('alone')
, ('alpha')
, ('already')
, ('also')
, ('alter')
, ('always')
, ('amateur')
, ('amazing')
, ('among')
, ('amount')
, ('amused')
, ('analyst')
, ('anchor')
, ('ancient')
, ('anger')
, ('angle')
, ('angry')
, ('animal')
, ('ankle')
, ('announce')
, ('annual')
, ('another')
, ('answer')
, ('antenna')
, ('antique')
, ('anxiety')
, ('any')
, ('apart')
, ('apology')
, ('appear')
, ('apple')
, ('approve')
, ('april')
, ('arch')
, ('arctic')
, ('area')
, ('arena')
, ('argue')
, ('arm')
, ('armed')
, ('armor')
, ('army')
, ('around')
, ('arrange')
, ('arrest')
, ('arrive')
, ('arrow')
, ('art')
, ('artefact')
, ('artist')
, ('artwork')
, ('ask')
, ('aspect')
, ('assault')
, ('asset')
, ('assist')
, ('assume')
, ('asthma')
, ('athlete')
, ('atom')
, ('attack')
, ('attend')
, ('attitude')
, ('attract')
, ('auction')
, ('audit')
, ('august')
, ('aunt')
, ('author')
, ('auto')
, ('autumn')
, ('average')
, ('avocado')
, ('avoid')
, ('awake')
, ('aware')
, ('away')
, ('awesome')
, ('awful')
, ('awkward')
, ('axis')
, ('baby')
, ('bachelor')
, ('bacon')
, ('badge')
, ('bag')
, ('balance')
, ('balcony')
, ('ball')
, ('bamboo')
, ('banana')
, ('banner')
, ('bar')
, ('barely')
, ('bargain')
, ('barrel')
, ('base')
, ('basic')
, ('basket')
, ('battle')
, ('beach')
, ('bean')
, ('beauty')
, ('because')
, ('become')
, ('beef')
, ('before')
, ('begin')
, ('behave')
, ('behind')
, ('believe')
, ('below')
, ('belt')
, ('bench')
, ('benefit')
, ('best')
, ('betray')
, ('better')
, ('between')
, ('beyond')
, ('bicycle')
, ('bid')
, ('bike')
, ('bind')
, ('biology')
, ('bird')
, ('birth')
, ('bitter')
, ('black')
, ('blade')
, ('blame')
, ('blanket')
, ('blast')
, ('bleak')
, ('bless')
, ('blind')
, ('blood')
, ('blossom')
, ('blouse')
, ('blue')
, ('blur')
, ('blush')
, ('board')
, ('boat')
, ('body')
, ('boil')
, ('bomb')
, ('bone')
, ('bonus')
, ('book')
, ('boost')
, ('border')
, ('boring')
, ('borrow')
, ('boss')
, ('bottom')
, ('bounce')
, ('box')
, ('boy')
, ('bracket')
, ('brain')
, ('brand')
, ('brass')
, ('brave')
, ('bread')
, ('breeze')
, ('brick')
, ('bridge')
, ('brief')
, ('bright')
, ('bring')
, ('brisk')
, ('broccoli')
, ('broken')
, ('bronze')
, ('broom')
, ('brother')
, ('brown')
, ('brush')
, ('bubble')
, ('buddy')
, ('budget')
, ('buffalo')
, ('build')
, ('bulb')
, ('bulk')
, ('bullet')
, ('bundle')
, ('bunker')
, ('burden')
, ('burger')
, ('burst')
, ('bus')
, ('business')
, ('busy')
, ('butter')
, ('buyer')
, ('buzz')
, ('cabbage')
, ('cabin')
, ('cable')
, ('cactus')
, ('cage')
, ('cake')
, ('call')
, ('calm')
, ('camera')
, ('camp')
, ('can')
, ('canal')
, ('cancel')
, ('candy')
, ('cannon')
, ('canoe')
, ('canvas')
, ('canyon')
, ('capable')
, ('capital')
, ('captain')
, ('car')
, ('carbon')
, ('card')
, ('cargo')
, ('carpet')
, ('carry')
, ('cart')
, ('case')
, ('cash')
, ('casino')
, ('castle')
, ('casual')
, ('cat')
, ('catalog')
, ('catch')
, ('category')
, ('cattle')
, ('caught')
, ('cause')
, ('caution')
, ('cave')
, ('ceiling')
, ('celery')
, ('cement')
, ('census')
, ('century')
, ('cereal')
, ('certain')
, ('chair')
, ('chalk')
, ('champion')
, ('change')
, ('chaos')
, ('chapter')
, ('charge')
, ('chase')
, ('chat')
, ('cheap')
, ('check')
, ('cheese')
, ('chef')
, ('cherry')
, ('chest')
, ('chicken')
, ('chief')
, ('child')
, ('chimney')
, ('choice')
, ('choose')
, ('chronic')
, ('chuckle')
, ('chunk')
, ('churn')
, ('cigar')
, ('cinnamon')
, ('circle')
, ('citizen')
, ('city')
, ('civil')
, ('claim')
, ('clap')
, ('clarify')
, ('claw')
, ('clay')
, ('clean')
, ('clerk')
, ('clever')
, ('click')
, ('client')
, ('cliff')
, ('climb')
, ('clinic')
, ('clip')
, ('clock')
, ('clog')
, ('close')
, ('cloth')
, ('cloud')
, ('clown')
, ('club')
, ('clump')
, ('cluster')
, ('clutch')
, ('coach')
, ('coast')
, ('coconut')
, ('code')
, ('coffee')
, ('coil')
, ('coin')
, ('collect')
, ('color')
, ('column')
, ('combine')
, ('come')
, ('comfort')
, ('comic')
, ('common')
, ('company')
, ('concert')
, ('conduct')
, ('confirm')
, ('congress')
, ('connect')
, ('consider')
, ('control')
, ('convince')
, ('cook')
, ('cool')
, ('copper')
, ('copy')
, ('coral')
, ('core')
, ('corn')
, ('correct')
, ('cost')
, ('cotton')
, ('couch')
, ('country')
, ('couple')
, ('course')
, ('cousin')
, ('cover')
, ('coyote')
, ('crack')
, ('cradle')
, ('craft')
, ('cram')
, ('crane')
, ('crash')
, ('crater')
, ('crawl')
, ('crazy')
, ('cream')
, ('credit')
, ('creek')
, ('crew')
, ('cricket')
, ('crime')
, ('crisp')
, ('critic')
, ('crop')
, ('cross')
, ('crouch')
, ('crowd')
, ('crucial')
, ('cruel')
, ('cruise')
, ('crumble')
, ('crunch')
, ('crush')
, ('cry')
, ('crystal')
, ('cube')
, ('culture')
, ('cup')
, ('cupboard')
, ('curious')
, ('current')
, ('curtain')
, ('curve')
, ('cushion')
, ('custom')
, ('cute')
, ('cycle')
, ('dad')
, ('damage')
, ('damp')
, ('dance')
, ('danger')
, ('daring')
, ('dash')
, ('daughter')
, ('dawn')
, ('day')
, ('deal')
, ('debate')
, ('debris')
, ('decade')
, ('december')
, ('decide')
, ('decline')
, ('decorate')
, ('decrease')
, ('deer')
, ('defense')
, ('define')
, ('defy')
, ('degree')
, ('delay')
, ('deliver')
, ('demand')
, ('demise')
, ('denial')
, ('dentist')
, ('deny')
, ('depart')
, ('depend')
, ('deposit')
, ('depth')
, ('deputy')
, ('derive')
, ('describe')
, ('desert')
, ('design')
, ('desk')
, ('despair')
, ('destroy')
, ('detail')
, ('detect')
, ('develop')
, ('device')
, ('devote')
, ('diagram')
, ('dial')
, ('diamond')
, ('diary')
, ('dice')
, ('diesel')
, ('diet')
, ('differ')
, ('digital')
, ('dignity')
, ('dilemma')
, ('dinner')
, ('dinosaur')
, ('direct')
, ('dirt')
, ('disagree')
, ('discover')
, ('disease')
, ('dish')
, ('dismiss')
, ('disorder')
, ('display')
, ('distance')
, ('divert')
, ('divide')
, ('divorce')
, ('dizzy')
, ('doctor')
, ('document')
, ('dog')
, ('doll')
, ('dolphin')
, ('domain')
, ('donate')
, ('donkey')
, ('donor')
, ('door')
, ('dose')
, ('double')
, ('dove')
, ('draft')
, ('dragon')
, ('drama')
, ('drastic')
, ('draw')
, ('dream')
, ('dress')
, ('drift')
, ('drill')
, ('drink')
, ('drip')
, ('drive')
, ('drop')
, ('drum')
, ('dry')
, ('duck')
, ('dumb')
, ('dune')
, ('during')
, ('dust')
, ('dutch')
, ('duty')
, ('dwarf')
, ('dynamic')
, ('eager')
, ('eagle')
, ('early')
, ('earn')
, ('earth')
, ('easily')
, ('east')
, ('easy')
, ('echo')
, ('ecology')
, ('economy')
, ('edge')
, ('edit')
, ('educate')
, ('effort')
, ('egg')
, ('eight')
, ('either')
, ('elbow')
, ('elder')
, ('electric')
, ('elegant')
, ('element')
, ('elephant')
, ('elevator')
, ('elite')
, ('else')
, ('embark')
, ('embody')
, ('embrace')
, ('emerge')
, ('emotion')
, ('employ')
, ('empower')
, ('empty')
, ('enable')
, ('enact')
, ('end')
, ('endless')
, ('endorse')
, ('enemy')
, ('energy')
, ('enforce')
, ('engage')
, ('engine')
, ('enhance')
, ('enjoy')
, ('enlist')
, ('enough')
, ('enrich')
, ('enroll')
, ('ensure')
, ('enter')
, ('entire')
, ('entry')
, ('envelope')
, ('episode')
, ('equal')
, ('equip')
, ('era')
, ('erase')
, ('erode')
, ('erosion')
, ('error')
, ('erupt')
, ('escape')
, ('essay')
, ('essence')
, ('estate')
, ('eternal')
, ('ethics')
, ('evidence')
, ('evil')
, ('evoke')
, ('evolve')
, ('exact')
, ('example')
, ('excess')
, ('exchange')
, ('excite')
, ('exclude')
, ('excuse')
, ('execute')
, ('exercise')
, ('exhaust')
, ('exhibit')
, ('exile')
, ('exist')
, ('exit')
, ('exotic')
, ('expand')
, ('expect')
, ('expire')
, ('explain')
, ('expose')
, ('express')
, ('extend')
, ('extra')
, ('eye')
, ('eyebrow')
, ('fabric');

INSERT INTO dbo.WordList
VALUES ('lab')
, ('label')
, ('labor')
, ('ladder')
, ('lady')
, ('lake')
, ('lamp')
, ('language')
, ('laptop')
, ('large')
, ('later')
, ('latin')
, ('laugh')
, ('laundry')
, ('lava')
, ('law')
, ('lawn')
, ('lawsuit')
, ('layer')
, ('lazy')
, ('leader')
, ('leaf')
, ('learn')
, ('leave')
, ('lecture')
, ('left')
, ('leg')
, ('legal')
, ('legend')
, ('leisure')
, ('lemon')
, ('lend')
, ('length')
, ('lens')
, ('leopard')
, ('lesson')
, ('letter')
, ('level')
, ('liar')
, ('liberty')
, ('library')
, ('license')
, ('life')
, ('lift')
, ('light')
, ('like')
, ('limb')
, ('limit')
, ('link')
, ('lion')
, ('liquid')
, ('list')
, ('little')
, ('live')
, ('lizard')
, ('load')
, ('loan')
, ('lobster')
, ('local')
, ('lock')
, ('logic')
, ('lonely')
, ('long')
, ('loop')
, ('lottery')
, ('loud')
, ('lounge')
, ('love')
, ('loyal')
, ('lucky')
, ('luggage')
, ('lumber')
, ('lunar')
, ('lunch')
, ('luxury')
, ('lyrics')
, ('machine')
, ('mad')
, ('magic')
, ('magnet')
, ('maid')
, ('mail')
, ('main')
, ('major')
, ('make')
, ('mammal')
, ('man')
, ('manage')
, ('mandate')
, ('mango')
, ('mansion')
, ('manual')
, ('maple')
, ('marble')
, ('march')
, ('margin')
, ('marine')
, ('market')
, ('marriage')
, ('mask')
, ('mass')
, ('master')
, ('match')
, ('material')
, ('math')
, ('matrix')
, ('matter')
, ('maximum')
, ('maze')
, ('meadow')
, ('mean')
, ('measure')
, ('meat')
, ('mechanic')
, ('medal')
, ('media')
, ('melody')
, ('melt')
, ('member')
, ('memory')
, ('mention')
, ('menu')
, ('mercy')
, ('merge')
, ('merit')
, ('merry')
, ('mesh')
, ('message')
, ('metal')
, ('method')
, ('middle')
, ('midnight')
, ('milk')
, ('million')
, ('mimic')
, ('mind')
, ('minimum')
, ('minor')
, ('minute')
, ('miracle')
, ('mirror')
, ('misery')
, ('miss')
, ('mistake')
, ('mix')
, ('mixed')
, ('mixture')
, ('mobile')
, ('model')
, ('modify')
, ('mom')
, ('moment')
, ('monitor')
, ('monkey')
, ('monster')
, ('month')
, ('moon')
, ('moral')
, ('more')
, ('morning')
, ('mosquito')
, ('mother')
, ('motion')
, ('motor')
, ('mountain')
, ('mouse')
, ('move')
, ('movie')
, ('much')
, ('muffin')
, ('mule')
, ('multiply')
, ('muscle')
, ('museum')
, ('mushroom')
, ('music')
, ('must')
, ('mutual')
, ('myself')
, ('mystery')
, ('myth')
, ('naive')
, ('name')
, ('napkin')
, ('narrow')
, ('nasty')
, ('nation')
, ('nature')
, ('near')
, ('neck')
, ('need')
, ('negative')
, ('neglect')
, ('neither')
, ('nephew')
, ('nerve')
, ('nest')
, ('net')
, ('network')
, ('neutral')
, ('never')
, ('news')
, ('next')
, ('nice')
, ('night')
, ('noble')
, ('noise')
, ('nominee')
, ('noodle')
, ('normal')
, ('north')
, ('nose')
, ('notable')
, ('note')
, ('nothing')
, ('notice')
, ('novel')
, ('now')
, ('nuclear')
, ('number')
, ('nurse')
, ('nut')
, ('oak')
, ('obey')
, ('object')
, ('oblige')
, ('obscure')
, ('observe')
, ('obtain')
, ('obvious')
, ('occur')
, ('ocean')
, ('october')
, ('odor')
, ('off')
, ('offer')
, ('office')
, ('often')
, ('oil')
, ('okay')
, ('old')
, ('olive')
, ('olympic')
, ('omit')
, ('once')
, ('one')
, ('onion')
, ('online')
, ('only')
, ('open')
, ('opera');

INSERT INTO dbo.WordList
VALUES ('vacant')
, ('vacuum')
, ('vague')
, ('valid')
, ('valley')
, ('valve')
, ('van')
, ('vanish')
, ('vapor')
, ('various')
, ('vast')
, ('vault')
, ('vehicle')
, ('velvet')
, ('vendor')
, ('venture')
, ('venue')
, ('verb')
, ('verify')
, ('version')
, ('very')
, ('vessel')
, ('veteran')
, ('viable')
, ('vibrant')
, ('vicious')
, ('victory')
, ('video')
, ('view')
, ('village')
, ('vintage')
, ('violin')
, ('virtual')
, ('virus')
, ('visa')
, ('wild')
, ('will')
, ('win')
, ('window')
, ('wine')
, ('wing')
, ('wink')
, ('winner')
, ('winter')
, ('wire')
, ('wisdom')
, ('wise')
, ('wish')
, ('witness')
, ('wolf')
, ('woman')
, ('wonder')
, ('wood')
, ('wool')
, ('word')
, ('work')
, ('world')
, ('worry')
, ('worth')
, ('wrap')
, ('wreck')
, ('wrestle')
, ('wrist')
, ('write')
, ('wrong')
, ('yard')
, ('year')
, ('yellow')
, ('you')
, ('young')
, ('youth')
, ('zebra')
, ('zero')
, ('zone')
, ('zoo');

To use the wordlist above, you could do something like this:

DECLARE @WordListCount int;
SELECT @WordListCount = COUNT(1)
FROM dbo.WordList;

UPDATE dbo.MyTable
SET dbo.MyTable.[First Name] = wlFirst.Word
    , dbo.MyTable.[Last Name] = wlLast.Word
FROM dbo.MyTable mt
    INNER JOIN (SELECT wl.Word, rn = ROW_NUMBER() OVER (ORDER BY wl.OrderBy) FROM dbo.WordList wl) wlFirst ON (mt.MyTableID % @WordListCount) = wlFirst.rn
    INNER JOIN (SELECT wl.Word, rn = ROW_NUMBER() OVER (ORDER BY wl.OrderBy) FROM dbo.WordList wl) wlLast ON (mt.MyTableID % @WordListCount) = wlLast.rn
GO

The results:

SELECT *
FROM dbo.MyTable;
╔═══════════╦═══════════════╦════════════╦═══════════╗
║ MyTableID ║ Date of Birth ║ First Name ║ Last Name ║
╠═══════════╬═══════════════╬════════════╬═══════════╣
║         1 ║ 1990-01-01    ║ kidney     ║ copy      ║
║         2 ║ 1989-01-01    ║ input      ║ mirror    ║
╚═══════════╩═══════════════╩════════════╩═══════════╝

1 - I cannot include the entire BIP-0039 wordlist here since the body of an answer is limited to 30,000 characters

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy