Solved

Composite column idea needed

Posted on 2009-04-02
11
186 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
  • 6
  • 4
11 Comments
 
LVL 16

Expert Comment

by:Auric1983
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 7

Author Comment

by:Camillia
Comment Utility
0
 
LVL 16

Expert Comment

by:Auric1983
Comment Utility
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 7

Author Comment

by:Camillia
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
sure, if both your columns are indexed it shouldn't add that much overhead to your search
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
Thanks..

 Chap: any other thoughts?
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
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 …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video discusses moving either the default database or any database to a new volume.

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now