Expanding short descriptions into long

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)

Expanding short descriptions into long

Author

Topic

offspring22
Starting Member

38 Posts

Posted - 2011-11-29 : 15:43:00

Hello,

I have a field called RegisteredSizeIncl, which has 0-5 short descriptions of 10 different possibilities, that I need to expand into long descriptions. For example, A sample of the field RegisteredSizeIncl is:

ATTGR,IAGR
ATTGR,IAGR,BLWGR
IAGR
WT,BLWGR,IAGR,ATTGR

The 10 possiblities are:

ATTGR attached garage
BALTR balcony/terrace
BLWGR below grade area
DETGR detached garage
IAGR interior above grade
INDPK indoor parking stall
LAND land
OUTPK outdoor parking stall
STORE storage area
WT wall thickness

So the 4 above examples would become:

attached garage, interior above grade
attached garage, interior above grade, below grade area
interior above grade
wall thickness,below grade area, interior above grade, attached garage

I'm not sure how to go about this really.... Any tips or suggestions?

Thanks!

offspring22
Starting Member

38 Posts

Posted - 2011-11-29 : 17:43:44

So I figured it out..... Somewhat. This does most the job:

replace
(replace
(replace
(replace
(replace
(replace
(replace
(replace
(replace
(replace(RegisteredSizeIncl,'ATTGR', ' attached garage')
,'BALTR', ' balcony/terrace')
,'BLWGR', ' below grade area')
,'DETGR', ' detached garage')
,'IAGR', ' interior above grade')
,'INDPK', ' indoor parking stall')
,'LAND', ' land')
,'OUTPK', ' outdoor parking stall')
,'STORE', ' storage area')
,'WT', ' wall thickness')

The one problem though, is it needs to be a sentence with hopefully proper grammar. So the above gives me the results of: " interior above grade, below grade area, wall thickness, balcony/terrace, attached garage". Any suggestions on how to replace the last , with "and"?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-11-29 : 18:05:07

I'd be tempted to split that string using a string splitting function (there are many you could search for - I recommend the number table based ones).

This would give you one row per code.

Then build a lookup table of code's to human readable descriptions. Just JOIN to this table once you've done the splitting.

Return the data to the calling application in a sensible order (so you'd have repeated values for the key of the result set)

Then let the front end code handle recombining them by iterating over the result set -- it's what front ends are good for.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources