?
Solved

Composite column idea needed

Posted on 2009-04-02
11
Medium Priority
?
229 Views
Last Modified: 2012-05-06
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?
0
Comment
Question by:Camillia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 16

Expert Comment

by:Auric1983
ID: 24052503
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 24052506
I assume you mean parsing the string the user enters and then searching on the two different columns independently, right?
0
 
LVL 7

Author Comment

by:Camillia
ID: 24052819
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Author Comment

by:Camillia
ID: 24052868
0
 
LVL 16

Expert Comment

by:Auric1983
ID: 24052981
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
 
LVL 7

Author Comment

by:Camillia
ID: 24052983
but how would a "calculated" column work in doing a search?? so, user enters UK1234. I have a calculated column..then what??
0
 
LVL 16

Accepted Solution

by:
Auric1983 earned 2000 total points
ID: 24052994
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
 
LVL 7

Author Comment

by:Camillia
ID: 24053042
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
 
LVL 16

Expert Comment

by:Auric1983
ID: 24053053
sure, if both your columns are indexed it shouldn't add that much overhead to your search
0
 
LVL 7

Author Comment

by:Camillia
ID: 24053114
Thanks..

 Chap: any other thoughts?
0
 
LVL 7

Author Comment

by:Camillia
ID: 24055498
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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question