Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Composite column idea needed

Posted on 2009-04-02
11
Medium Priority
?
233 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
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Loops Section Overview

926 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