Conversion failed when converting the nvarchar value 'Name' to data ty 1

[JeanneZ]

Hi, SQL expert, I have a question to consult you.
We have a table with fields to conbine user first name, last name and company name, and other information. There are two fields related, propertynames, propertyvalues. The two columns are ntext data type.
Some data sample like:
propertynames: FirstName:S:0:7:LastName:S:7:5:CompanyName:S:12:5:
propertyvalues: JeffreyMcGeeIntel
I need to add a filter for compnay name.
So my SQL code is:
substring(propertyvalues, Cast(substring(substring(propertynames, CHARINDEX('companyname', propertynames)+len('companyname:s:'), DATALENGTH(propertynames)),1, CHARINDEX(':',substring(propertynames, CHARINDEX('companyname', propertynames)+len('companyname:s:'), DATALENGTH(propertynames)))-1) as int)+1,
cast(substring(substring(substring(propertynames, CHARINDEX('companyname', propertynames)+len('companyname:s:'), DATALENGTH(propertynames)), CHARINDEX(':',substring(propertynames, CHARINDEX('companyname', propertynames)+len('companyname:s:'), DATALENGTH(propertynames)))+1, DATALENGTH(propertynames)),1, CHARINDEX(':',substring(substring(propertynames, CHARINDEX('companyname', propertynames)+len('companyname:s:'), DATALENGTH(propertynames)), CHARINDEX(':',substring(propertynames, CHARINDEX('companyname', propertynames)+len('companyname:s:'), DATALENGTH(propertynames)))+1, DATALENGTH(propertynames)))-1) as int))
It is successfully like all company name. But when I add a filter to get company = 'Intel' I got error:"Conversion failed when converting the nvarchar value 'Name' to data type int." I am not sure what proble is. Please help me to solve the problem.
Thanks a lot for any help.
Jeanne

 

[gmmastros]

I beg you.... please do some reading...

[google]Database Normalization[/google]



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

What if the data were imported this way?

In any case, the query itself is not readable - you need to first split it to proper fields (may be varchar(100) or less for each name part) and only then you would be able to manipulate them.

 

[hmckillop]

It looks like the default schema for the ASPNET_membership database, if so use one of the stored procedures to filter the data, there are many provided.

Secondly, the actual problem you are getting is that certain string functions can not be applied to NTEXT data types, as such anywhere you use these functions you need to do an explicit conversion to nvarchar first.

Lastly - agree with George.



"I'm living so far beyond my income that we may almost be said to be living apart

 

[SQLSister]

At the heart of it, you have a poor design. There is no good solution except to get rid of this bad design and get one that is normalized. The more you try to do work arounds to get this to work the worse performance will be. There is no substitue for storing information correctly and this is possibly the worst structure I've ever seen in over 30 years of dealing with hundreds of databases.


"NOTHING is more important in a database than integrity." ESquared

 

[JeanneZ]

Thanks a lot for you answering my question. Yes, it is bad design. I will talk with DBA for the solution. Again, thanks a lot for you all.

 

[hmckillop]

SQLSister - have you seen the default ASP_net membership schema, as produced by Microsoft - it does exactly what JeanneZ posted. They must have had a reason for it - though I cant explain what.


"I'm living so far beyond my income that we may almost be said to be living apart