Which method is better for performance?
I have a identity field that holds order numbers.
I have another table that holds company code.
I concat company code + order number . So I have XYZ1234 or ABC56879
All fine. Now, user wants to search by entering ABC56879.
My options:
1. Have a function to parse out ABC and use 56879 and search on that identity column.
2. Create a new column. Concat company code + identity field. Use this new column to search.
which one is better/faster?
I have another table that holds company code.
I concat company code + order number . So I have XYZ1234 or ABC56879
All fine. Now, user wants to search by entering ABC56879.
My options:
1. Have a function to parse out ABC and use 56879 and search on that identity column.
2. Create a new column. Concat company code + identity field. Use this new column to search.
which one is better/faster?
ASKER
cant use computed column. To concat CompanyCode + OrderNumber, i have to join 2 tables.
Been researching this all day :(
so, go with #2 , new column, not a function?
Been researching this all day :(
so, go with #2 , new column, not a function?
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
>cant use computed column.
yes, you can. with "computed column" I don't mean "computed inside a query", but a column in the table:
yes, you can. with "computed column" I don't mean "computed inside a query", but a column in the table:
ALTER TABLE yourtable ADD COLUMN CombinedOrder = Company_Code + CAST(order_number as varchar(30))
ASKER
let me try it. This is like creating a new column. I will post back. Let me read about "computed column".
ASKER
That line gives me an error.
But here, i cant have a computed column on TWO tables. That "Company_Code" is coming from another table.
http://msdn.microsoft.com/en-us/library/ms191250.aspx
But here, i cant have a computed column on TWO tables. That "Company_Code" is coming from another table.
http://msdn.microsoft.com/en-us/library/ms191250.aspx
I understand that the 2 fields are in 2 tables.
so, how is that supposed to work for the "search" ?
what is the link between the 2 tables?
so, how is that supposed to work for the "search" ?
what is the link between the 2 tables?
ASKER
the link between the 2 tables is an ID.
For the search, if user enters XYZ1234, i can do a select on the composite column to get the result.. select * from table having (code+number)
no? I've probably missed the boat totally on this..
I can go with a new column, and on insert, just concat code+number and dump the values there...
For the search, if user enters XYZ1234, i can do a select on the composite column to get the result.. select * from table having (code+number)
no? I've probably missed the boat totally on this..
I can go with a new column, and on insert, just concat code+number and dump the values there...
last clarifications:
how to "split" the search value?
will it ALWAYS be 3 characters + some digits, or should be take all the characters for the company code and the digits for the number?
how to "split" the search value?
will it ALWAYS be 3 characters + some digits, or should be take all the characters for the company code and the digits for the number?
ASKER
any character for the company. I found this function to parse out the non-numeric and just spit out digits. From Scott Pletcher on this forum.
ALTER FUNCTION [dbo].[fn_strip_nonnumeric] (
@string VARCHAR(1000)
)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @SiteCode INT
SET @SiteCode = PATINDEX('%[^0-9]%', @string)
WHILE @SiteCode > 0
BEGIN
SET @string = LEFT(@string, @SiteCode - 1) + SUBSTRING(@string, @SiteCode + 1, 1000)
SET @SiteCode = PATINDEX('%[^0-9]%', @string)
END --WHILE
RETURN @string
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sounds like it's exactly what you are looking for.