[dba-SQLServer] How good are you really?

John Colby jwcolby at ColbyConsulting.com
Thu Nov 17 10:49:46 CST 2005


The challenge:

DotNetNuke stores it's user info in a denormalized field in a table in SQL
Server.  One field has the definition of the data, for that record, at that
instant:

Website:S:0:0:TimeZone:S:0:4:PreferredLocale:S:4:5:Region:S:9:7:FirstName:S:
16:6:PostalCode:S:22:5:Street:S:27:22:LastName:S:49:11:Unit:S:60:0:Telephone
:S:60:12:Country:S:72:13:IM:S:85:0:Fax:S:85:0:City:S:85:8:Cell:S:93:12:

With the data itself as follows:

 -480en-USMontanaLouise82637918 Keller Dr. Box
642Hammersmark307-436-9731United StatesGlenrock307-259-8423

As you can see, the data is really a set of fields, with the field names
embedded in the definition, then a start position within the data string,
and the length of the data actually in the string.

Is it possible to build a query or SP to pull the data out of the data
field, using the formatting data and return a table of the data with field
names and the data available?

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/





More information about the dba-SQLServer mailing list