multiple Column processing - SQL Server Forums

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)

multiple Column processing

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-07 : 10:14:28

dip writes "I want to illustrate my problem with a simple example:

I have 4 columns in a table.
They are:
Apt_Number
ZipCode
City
Country

I wish to write a 'Select' statement which should return me
a resultset containing the following:
all the Zipcodes reversed and appended with an integer which
is a sum of all the digits present in the apt number.

Eg:
ZipCode = 12350
Apt_Number = 789
I expect:
0532124
****The last two digits(24) is actually the sum of all the
digits of the apt number (7+8+9) and first five digits is the
zipcode in reverse order(12350 -> 05321)*********

I expect a Select statement that would retrieve all the
columns, process it according to the logic and gives back
all the results.

Please let me know if that's possible.

In case we cannot write a SELECT then how would write a
SQL function, StoredProcedure to achieve the same? I do not
want to use C, Java or any other native language function
to be doing the processing.

The above is just an example, finally I would like to do
processing on multiple columns and some complicated logic.
Please let me know whether any solution exists which does
not have a performance bottleneck

Dip. "

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-07 : 10:30:12

reverse does what it says.
Sum of digits in apt_number - probably use a function
declare @i int, @j int, @s varchar(100)
set @i = 1
set @j = 0
while @i <= len(@apt_number)
begin
set @j = @j + convert(int,substring(@apt_number,@i,1))
end
select @s = convert(varchar(100),@j)

then
select reverse(zipcode) + fn(apt_number)

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Nazim
A custom title

1408 Posts

Posted - 2002-01-07 : 10:33:23

Put this into a function

declare @mNo as Integer
declare @Rem as integer
declare @SumDig as integer

set @SumDig=0

while @mNo>0
begin
set @Rem=@mNo%10
set @mNo=@mNo/10
set @SumDig=@SumDig+@Rem
end

select reverse(str(@zip))+@SumDig
end

HTH

----------------------------
Anything that Doesn't Kills you Makes you Stronger

Edited by - Nazim on 01/07/2002 10:36:52

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources