how to add two fields together

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 2005 Forums

Transact-SQL (2005)

how to add two fields together

Author

Topic

pinoyextacy
Starting Member

15 Posts

Posted - 2008-07-14 : 13:54:52

I need to know how to add two integer fields and have the spaces front and back of it eleminate and only the value of it to eleminate the zeros in front.

For example:
I want to combine Client_number + Client_id in which they are on two different fields, so I can get something like 7777 for client_number and 888 for client id making it 7777888 stored into a client_sum.
In addition, if there is any spaces __7777___ in front and behind to be eleminated. Furthermore, if there is 0007777 to truncate it so it only shows 7777.

I am wondering if there is a way to do this in sql

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-14 : 14:04:55

try this out:-
ltrim(rtrim(case when patindex('000%',cast(Client_number as varchar(10)))>0 then substring(cast(Client_number as varchar(10)),patindex('000%',cast(Client_number as varchar(10)))+3,len(Client_number) else cast(Client_number as varchar(10)) end))+ cast(Client_id as varchar(10))

tatasmove
Starting Member

4 Posts

Posted - 2008-07-14 : 14:06:34

If you are adding 2 INTEGER fields I dont think you will get the results of 77778888? Instead you will get this 16665..?Based on my understanding, but if you are adding or concatinates a string '7777' and '8888' you will get this result ' 77778888' and if there are trailing spaces you need to use the built in function of SQL LTRIM and RTRIM to trim the spaces.

pinoyextacy
Starting Member

15 Posts

Posted - 2008-07-14 : 14:17:04

I am trying to concatinate it a string but how do I store those two field values into a field called client_sum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-14 : 14:18:38

quote:
Originally posted by pinoyextacy

I am trying to concatinate it a string but how do I store those two field values into a field called client_sum

UPDATE YourTable
SET client_sum=ltrim(rtrim(case when patindex('000%',cast(Client_number as varchar(10)))>0 then substring(cast(Client_number as varchar(10)),patindex('000%',cast(Client_number as varchar(10)))+3,len(Client_number)) else cast(Client_number as varchar(10)) end))+ cast(Client_id as varchar(10))

pinoyextacy
Starting Member

15 Posts

Posted - 2008-07-15 : 15:06:05

When I run that script I get an error:

[Sybase][ODBC Driver][Adapative Server Anywhere]Syntax error or access
violation: near 'else' in...)))+3, len(clt_number) [else] cast(clt_number as...

I don't know where the error is... Please help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-15 : 15:07:38

quote:
Originally posted by pinoyextacy

When I run that script I get an error:

[Sybase][ODBC Driver][Adapative Server Anywhere]Syntax error or access
violation: near 'else' in...)))+3, len(clt_number) [else] cast(clt_number as...

I don't know where the error is... Please help

Aha...you're in wrong forum then. This is MS SQL Server forum please post in some sybase related forums if you want sybase specific solution.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 15:18:29

Try this

DECLARE	@Number VARCHAR(20),
	@ID VARCHAR(20)

SELECT	@Number = '0007777',
	@ID = '023120'

SELECT	REPLACE(LTRIM(REPLACE(@Number, '0', ' ')), ' ', '0') + REPLACE(LTRIM(REPLACE(@ID, '0', ' ')), ' ', '0')

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources