combine 2 varchars and convert to bigint? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

combine 2 varchars and convert to bigint?

Author

Topic

DTFan
Yak Posting Veteran

52 Posts

Posted - 2006-12-12 : 10:05:44

I inherited a database and have something I have to work around. My table has 13 fields and I need to query on 2 of them. Both fields are varchars but contain only numeric numbers. I have no idea why the person who designed it didn't use int or tinyint but he used varchar(3) and varchar(4).

tblUnitList
unitID (int, p/k)
unitName (varchar(30))
unitGrp (varchar(3))
unitNum (varchar(4))
...

What I need to do is combine the two fields (unitGrp and unitNum) into one field and make it an integer. The query that I am attempting to write is supposed to return only the highest number from the combined fields. Once I have that, then I can increment by one in my code to give the next available grp/num combination.

So, if I have:
unitID | unitName | unitGrp | unitNum
1 test1 132 1254
2 test2 154 3323
3 test3 999 1029
4 test4 345 9807

the query would return 9991029 as a bigint to my calling program (VB 2005). Then I could increment by 1 and offer the user 999 and 1030 (as their next available grp/num settings).

I've got the conversion working with this:


select unitid from tblUnit
where  convert(integer, unitGrp + '' + unitID) = 9991029

but I don't know how to get only 1 record returned (where I wouldn't be using the WHERE clause to specify a valid combination). I believe the above is correct because it's comparing the integer converted to an integer (no single quotes around the 9991029).

But how would I phrase a SELECT TOP on the conversion?

Any and all help is greatly appreciated. Thank you in advance for any help/suggestions/etc. I wish I could alter the table to use ints (or combine) but I can't so I have to work around what I have.

Thanks in advance.

DTFan
Ever-hopeful programmer-in-training

Kristen
Test

22859 Posts

Posted - 2006-12-12 : 10:17:28

Are unitGrp and unitID always 3 characters and 4 respectively? If unitGrp could be, say, 001 or unitID could be 0001 - or 99999 - then you will need to "combine" then to take this into account.

If unitID has a max of 9999 then you would need:

select unitid from tblUnit
where convert(integer, unitGrp) * 10000 + CONVERT(integer, unitID) = 9991029

"But how would I phrase a SELECT TOP on the conversion?"

Unless I've missed something I'm not sure why you want to do this - and understanding why may be key to suggesting how you should do it!

You might want:


select MAX(unitid)
from tblUnit
where  ... = 9991029

or you might want something more like:


select TOP 1 unitid
from tblUnit
where  ... = 9991029
ORDER BY MothersMaidenName

Kristen

DTFan
Yak Posting Veteran

52 Posts

Posted - 2006-12-12 : 10:40:51

quote:
Originally posted by Kristen
Are unitGrp and unitID always 3 characters and 4 respectively? If unitGrp could be, say, 001 or unitID could be 0001 - or 99999 - then you will need to "combine" then to take this into account.

Yes, as of now they are. From what I've been told they always will be. For unitGrp, the lowest number is 100. For unitNum, the lowest number is 1000.

quote:
Originally posted by Kristen
"But how would I phrase a SELECT TOP on the conversion?"

Unless I've missed something I'm not sure why you want to do this - and understanding why may be key to suggesting how you should do it!

You might want:

select MAX(unitid)
from tblUnit
where ... = 9991029

or you might want something more like:

select TOP 1 unitid
from tblUnit
where ... = 9991029
ORDER BY MothersMaidenName

Kristen

The reason I wanted only one returned is that when the user is adding a new unit, I need to give him/her the next available grp/num combination. There are gaps in the middle but that is how they want it. But when adding a new user, instead of them looking for whatever the highest combination is, they want me to provide it. So I'm not sure which would be better (max or top 1). Basically the stored procedure is going to get the highest number (from the two combined fields) and return that. Then I will increment that by one to give the next available grp/number to the user. But it's not the unitID that I need the Top 1 of (or Max of) ... it's the combination result of the two fields. Does that make any sense?

Knowing that, which is a better approach?

Thanks again for the help. It is greatly appreciated. I can honestly say I don't think I would have thought of including the * 10000 like you did. I can see how that could lead to problems later on so thanks for that also :)

DTFan
Ever-hopeful programmer-in-training

Kristen
Test

22859 Posts

Posted - 2006-12-12 : 14:48:23

"But it's not the unitID that I need the Top 1 of (or Max of) ... it's the combination result of the two fields. Does that make any sense?"

Mathematically "Yes I think so", logistically "Nope, makes no sense whatsoever!"

But either way you want to be able to calculate the next-available-number, and to my mind you need to use MAX [to get the curently-biggest-number] and add one to that.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources