Replacing features in a field with numerical codes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Replacing features in a field with numerical codes

Author

Topic

offspring22
Starting Member

38 Posts

Posted - 2012-04-10 : 16:39:44

Hey hey,

I need to do a pull of a bunch of fields, some of which are multiple values in one column in the form of a text short code, for example:

Feature:

BDLUG,DECK,EX2X6,NSMKE,SKYLT,DETEC,SWIRL
BALCY,DETEC,NSMKE,PATIO
DECK,GRNHS
DETEC,PATIO
ACCEN,CLSET,DETEC,INSUG,NOPET,NSMKE,SWIRL,VINYL,SPRKL
ACCEN,BALCY,CEIL9,NSMKE

Those are all one in one column - the comma is apart of the varchar data.

I need to change those to correspond numerical values. For example, DECK is 013, GRNHS is 073, ACCEN is 082, NSMKE is 018 etc, therefore the records above should be translated to:

007,013,015,018,022,024,021
005,024,018,017
013,073
024,017
082,027,024,024,023,018,021,030,035
082,005,016,018

I can create a lookup table mapping each 5 letter code to his 3 digit equivilant, but any idea of what to do after that? Suggestions? For S&G's, the lookupdate will be called LU_FEATURES, with the fields of SHORT and NUM for the field names. If it was a 1 for 1 replacement, that would be easy, but I need it to replace multiples which has me stumped.

Thanks!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-10 : 17:35:45

One way would need to split the comma-separated values into tokens, replace each token with the corresponding numeric code and then reassemble. Not 2 lines of code by any means.

Another way, albeit sort of low-tech is to just generate replace statements and run them. For example, run this code first:

CREATE TABLE #tmpLookupTable(alphaCode VARCHAR(32), numericCode VARCHAR(32));
INSERT INTO #tmpLookupTable VALUES ('DECK','013'),('GRNHS','073');

SELECT 'update yourTable set YourColumn = REPLACE(YourColum,''' + alphaCode + ''','''+numericCode+''')'
FROM #tmpLookupTable;

Now, take the output of that code and run that.

Of course, if you want to automate this, then there is more work to do.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources