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?
LVL 7
CamilliaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Auric1983Connect With a Mentor Commented:
select * from table having (countrycode+identity) = 'UK1234'

keep in mind if your Country code field is greater than 2 characters and is a CHAR datatype you will need to rtrim() the field to remove any whitespace at the end.
0
 
Auric1983Commented:
If your always using the identify field as the "number" there is no way it can be duplicated.  

if the user is always inputting the 6 character Country code + 4 digit ID i'd just search on the 4 digit ID
0
 
chapmandewCommented:
I assume you mean parsing the string the user enters and then searching on the two different columns independently, right?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
CamilliaAuthor Commented:
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.
0
 
CamilliaAuthor Commented:
0
 
Auric1983Commented:
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'
0
 
CamilliaAuthor Commented:
but how would a "calculated" column work in doing a search?? so, user enters UK1234. I have a calculated column..then what??
0
 
CamilliaAuthor Commented:
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??
0
 
Auric1983Commented:
sure, if both your columns are indexed it shouldn't add that much overhead to your search
0
 
CamilliaAuthor Commented:
Thanks..

 Chap: any other thoughts?
0
 
CamilliaAuthor Commented:
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.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.