Composite column idea needed
I have an identity field that increments , for example, 1234, 1235, 1236.
I have another column that has country code. When I pull up this number, I can concat the country code to the number: UK1234, US1235
So, 2 coulmns: one country code, one number.
**Now, the user enters UK1234 (not pulling up data, but searching for data). I'm thinking about parsing out the string section and just do a search for the number. Then see if that number matches the country-code.
Is there a better way of doing this?
I have another column that has country code. When I pull up this number, I can concat the country code to the number: UK1234, US1235
So, 2 coulmns: one country code, one number.
**Now, the user enters UK1234 (not pulling up data, but searching for data). I'm thinking about parsing out the string section and just do a search for the number. Then see if that number matches the country-code.
Is there a better way of doing this?
I assume you mean parsing the string the user enters and then searching on the two different columns independently, right?
ASKER
Auric1983 - that wont work. For example, we have 2 tabs on our screen. One for US, one for UK.
We have numbers : US1234 and UK5678. User goes to tab US and enters 5678 (this belongs to UK tho) and the wrong country code row is pulled up.
chapmandew: you're right.But my coworker says that's a slow search. He says maybe I can do a "calculated" search. Or even create a new column to hold US1234 but he says that's extra storage.
We have numbers : US1234 and UK5678. User goes to tab US and enters 5678 (this belongs to UK tho) and the wrong country code row is pulled up.
chapmandew: you're right.But my coworker says that's a slow search. He says maybe I can do a "calculated" search. Or even create a new column to hold US1234 but he says that's extra storage.
ASKER
i think this is what he means:
http://www.bottleit.com.au/blog/post/Use-a-computed-or-calculated-column-in-SQL-Server.aspx
http://www.bottleit.com.au/blog/post/Use-a-computed-or-calculated-column-in-SQL-Server.aspx
Is it two seperate queries that run each tab Farzadw?
If so you could just hardcode the country code for each tab
tab1 : select * from table where customerid=5678 and Country='UK'
tab2: select * from table where customerid=1234 and Country='US'
If so you could just hardcode the country code for each tab
tab1 : select * from table where customerid=5678 and Country='UK'
tab2: select * from table where customerid=1234 and Country='US'
ASKER
but how would a "calculated" column work in doing a search?? so, user enters UK1234. I have a calculated column..then what??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hardcoding is out of the question.
>> select * from table having (countrycode+identity) = 'UK1234'
so have a calculated column and that's how i do a search when user enters UK1234??
>> select * from table having (countrycode+identity) = 'UK1234'
so have a calculated column and that's how i do a search when user enters UK1234??
sure, if both your columns are indexed it shouldn't add that much overhead to your search
ASKER
Thanks..
Chap: any other thoughts?
Chap: any other thoughts?
ASKER
how do I do the computed column formual??
This is the sql statement;
select table1.code + cast (table2.number as varchar(20))
from table1 inner join table2 on table1.id = table2.sid
I tried that SQL statement as the computed field (note the computed field is Identity) but doesnt work.
This is the sql statement;
select table1.code + cast (table2.number as varchar(20))
from table1 inner join table2 on table1.id = table2.sid
I tried that SQL statement as the computed field (note the computed field is Identity) but doesnt work.
if the user is always inputting the 6 character Country code + 4 digit ID i'd just search on the 4 digit ID