June 22, 2009 at 10:19 am
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
June 26, 2009 at 12:01 am
This one won't be easily solved. I am going to need some additional information from you, and I'm going to ask you to read the first article I reference below in my signature block regarding asking for assistance.
I really need you to follow the instructions in that article if you want help. We need the DDL (create table statements) for the table(s) involved, sample data (in a readily consumable format that can be cut/paste/run in SSMS to populate the table(s) we create with the CREATE TABLE scripts you provide), expected results based on the sample data you provide (what should be returned from the query), your current code that you are having problems with at the moment (and please format it using appropriate white space, etc so that it is easier to read).
June 27, 2009 at 5:26 pm
Thanls a lot for you answering my question. It is bad design. I will ask team leader to alter table. Thanks.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply