Nightmare field...stripping a int from a varchar
-
I have a field in a table that is defined as a varchar and stores two seperate entries in it. It is always 4 characters long. Now the problems are that there are often leading zeros which need to be removed and other times there is an alphabetic character at the end that needs to be removed and stored
For example we may have some like this
Unit ID Lot Block
0012 needs to be 12 ''
010B needs to be 10 'B'
123F needs to be 123 'F'
0001 needs to be 1
I'm at my wits end...anybody had to do something like this before? I would love to put this into a function if possible.
Marty
-
Try this......
declare @table table (UnitID char(4))
insert into @table values('0012')
insert into @table values('010B')
insert into @table values('123F')
insert into @table values('0001')
select UnitID,
case
when right(UnitID, 1) like '[A-Z]' then convert(int, left(UnitID, 3))
else convert(int, UnitID)
end as Lot,
case
when right(UnitID, 1) like '[A-Z]' then right(UnitID, 1)
else ''
end as Block
from @table
-
Hey Paul,
This seems to work but how could I use it in a function?
-
Marty
You say that you would like to place this functionality into a User Defined Function, however there are a number of issues to consider about using something like this in a function.
1. Scaler Functions
The main problem you have with a scaler function in this situation is that thay can return only a single value and you are looking to pass in a single value (e.g. 123F) and expecting it to return two values (i.e. 123 & F). A work around would be to return them in a delimited string (e.g. 123|F), which would then have to be split out again in your calling location. To process a delimited string would either require two calls to the function (one for the left part, another for the right) or would require you to cursor through your resultset assigning the delimited string to a local variable prior to splitting it into its component parts. You should also consider that a function call would be made for every row of your source data. Each of these methods could have serious performance issues.
2. Table Functions
It is possible to create a UDF which can return the data in a table variable, which would suit your two/three columns. It would also be possible to use a table variable as an input param for your UDF which would allow you to pass in the source data. However, your calling procedure would have to preload all the source data into the temporary table variable prior to calling the UDF which would be quite a performance hit for large recordsets.
Conclusion:
In terms of performance the best solution is to code the example I provided above into the a batch operation. This would not use row-level function calls, cursors or temporary tables and therefore give you the best performance and most scalable solution.
Hope this helps, even if it isn't necessarily the answer you are looking for.
Edited by - paulhumphris on 06/03/2003 03:49:18 AM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply