2

I have a column that gives info on how to parse another column within the same table.The column has the property name and string length of the value that it appears in the next column. Here is how it looks:

PropertyNames PropertyValuesString SP_PartnerCode:S:0:14:FirstName:S:14:5:LastName:S:19:9: InvestProBase2rogerpatterson SP_PartnerCode:S:0:14:FirstName:S:14:5:LastName:S:19:7: InvestProBase2AaronSchmidt SP_PartnerCode:S:0:0:FirstName:S:0:6:LastName:S:6:9: JosephGaultieri SP_PartnerCode:S:0:14:FirstName:S:14:4:LastName:S:18:9: InvestProBase2ToddEdmondson SP_PartnerCode:S:0:14:FirstName:S:14:7:LastName:S:21:4: InvestProBase2MichaelLove

I want to separate this into a column per property name, like this:

SP_PartnerCode FirstName LastName InvestProBase2 roger patterson InvestProBase2 Aaron Schmidt Joseph Gaultieri InvestProBase1 Kevin Lemmon InvestProBase1 John Switzer InvestProBase2 bryan abbott InvestProBase2 Todd Edmondson InvestProBase2 Michael Love

Is this possible?

Improve this question
2
  • This needs a redesign. You will never be able to effectively query this mess. Go back and read about normalization and stop using a design like this. Rule one, never store more than one piece of information in a field. You broke rule 1 of database design. Not only will this be horrible to build queries for but it will be a performance hog. This is a system that should never be allowed to go to production. If you are asking this to parse the info from another sytem to put in your correclty normalized design, ignore my rant. Commented Aug 29, 2011 at 18:48
  • I assume this is external data? Have you considered using SSIS to transform the data to fit into your schema? Commented Aug 30, 2011 at 4:16

2 Answers 2

Reset to default
5

You should really normalize your data, was the first thing that came to my mind.

This function parses your data for a given property:

create function Parse(@property nvarchar(4000), @meta nvarchar(4000), @data nvarchar(4000)) returns nvarchar(4000) as begin set @meta = N':' + @meta declare @iproperty int, @itype int, @ibegin int, @ilength int, @iend int set @iproperty = charindex(N':' + @property + N':', @meta) if @iproperty = 0 return null set @itype = charindex(N':', @meta, @iproperty + 1) set @ibegin = charindex(N':', @meta, @itype + 1) set @ilength = charindex(N':', @meta, @ibegin + 1) set @iend = charindex(N':', @meta, @ilength + 1) declare @sbegin nvarchar(5), @slength nvarchar(5) set @sbegin = substring(@meta, @ibegin + 1, @ilength - @ibegin - 1) set @slength = substring(@meta, @ilength + 1, @iend - @ilength - 1) declare @begin int, @length int set @begin = convert(int, @sbegin) set @length = convert(int, @slength) if @length = 0 return null return substring(@data, @begin + 1, @length) end

and then SELECT your data

select dbo.Parse('SP_PartnerCode', PropertyNames, PropertyValuesString) as SP_PartnerCode, dbo.Parse('FirstName', PropertyNames, PropertyValuesString) as FirstName, dbo.Parse('LastName', PropertyNames, PropertyValuesString) as LastName from MyTable
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Wow devio, that worked flawlessly!! Now I can get to normalizing this mess. Thx!
3

You can use Substring

Improve this answer

1 Comment

substring would certainly work, but you may want to consider doing the split in your application or presentation layers. SQL is notoriously weak in string manipulation.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.