# Number Calculations - Concatenations RSS

## 8 replies

Last post Nov 26, 2012 11:27 PM by Bobby-Z

Contributor

2838 Points

1120 Posts

### Number Calculations - Concatenations

I am trying to code a stroed procedure that creates and ID column based upon a concatenation of 3 or 4 other numbers

I need the code to represent other id info and add them together

519 - dept code

23 product code

24 - client code

001 - company sku (unknown)

so the code needs to be 519+23+24+001 or 5192324001 NOT the addition of the numbers

That is my first problem, second If I know the first three numbers then I need to search the table for the last number

example if the last item for 5192324 is 005 I need to search for the last 5192324 NOT the last record in the db but the last record that begins with 5192324 then add 1 to it. SELECT @NewProductID = Product FROM Table WHERE ProductID LIKE '5192324___' ; SET @ProductID = @NewProductID + 1

First I would need to setect the last ID that Begins with 5192324 there could be 001 to 999

[SUMMARY]

Concat 510+23+24 So I can use that to find the last record that begins with 5102324

Is this understandable?

"Success is the Sum of Small Efforts, Repeated Day in and Day Out - Without Ceasing!"

Currently Learning: ASP, SQL, CSS, JavaScript, AJAX, XML, XSLT, C# So please be patient with me! Thanks
• Edited by Bobby-Z on Nov 21, 2012 04:39 AM

Member

32 Points

6 Posts

### Re: Number Calculations - Concatenations

http://dotnetdots.wordpress.com/2012/11/10/how-to-set-identity-keyauto-increment-on-varchar-column-in-sql-server/

-sameer

• Marked as answer by Bobby-Z on Nov 26, 2012 11:27 PM

Contributor

6195 Points

3214 Posts

### Re: Number Calculations - Concatenations

Try

...

where column like cast(510 as varchar)+cast(23 as varchar)+cast(24 as varchar) +'%'

Many Thanks & Best Regards,
HuaMin Chen
• Marked as answer by Bobby-Z on Nov 26, 2012 11:27 PM

Star

9968 Points

952 Posts

Microsoft

### Re: Number Calculations - Concatenations

Hi Bobby-z,

Please try to refer to the following code:

```create procedure testpro
@deptcode int,
@productcode int,
@clientcode int,
@companycode int
as
begin
declare @id varchar(20);
declare @id1 varchar(20);
declare @len int;
declare @len1 int;
declare @NewProductID int;

set @id=cast(@deptcode as varchar)+cast(@productcode as varchar)+cast(@clientcode as varchar)+cast(@companycode as varchar)
set @len1=@len-3;
set @id1=LEFT(@id,@len1);
SELECT @NewProductID = Product FROM Table WHERE ProductID LIKE @id1+'%' ;

end```

Thanks,
Amy Peng

Please mark the replies as answers if they help or unmark if not.
Feedback to us

Develop and promote your apps in Windows Store

• Marked as answer by Bobby-Z on Nov 26, 2012 11:27 PM

Contributor

3970 Points

1096 Posts

### Re: Number Calculations - Concatenations

#### Bobby-Z

I am trying to code a stroed procedure that creates and ID column based upon a concatenation of 3 or 4 other numbers

I need the code to represent other id info and add them together

519 - dept code

23 product code

24 - client code

001 - company sku (unknown)

so the code needs to be 519+23+24+001 or 5192324001 NOT the addition of the numbers

That is my first problem, second If I know the first three numbers then I need to search the table for the last number

example if the last item for 5192324 is 005 I need to search for the last 5192324 NOT the last record in the db but the last record that begins with 5192324 then add 1 to it. SELECT @NewProductID = Product FROM Table WHERE ProductID LIKE '5192324___' ; SET @ProductID = @NewProductID + 1

First I would need to setect the last ID that Begins with 5192324 there could be 001 to 999

[SUMMARY]

Concat 510+23+24 So I can use that to find the last record that begins with 5102324

Is this understandable?

I understand exactly what you are trying to do.  I also think it's a bad idea.

I think you would be better off storing the department, product and client in separate fields.  These would be part of your primary key and would all be foreign keys.  The last part you can generate on your own.  However, 3 digit numeric strings limits you to 999 records which might not be enough.

But that's just my opinion.

Member

452 Points

81 Posts

### Re: Number Calculations - Concatenations

Hi,

try this .

```BEGIN
--CONSIDERING ALL THESE ARE INTEGERS
DECLARE @Dept_Code INT, @Product_Code INT , @Client_Code INT , @Company_Code INT
DECLARE @ID BIGINT
--TEMPORARY TABLE
DECLARE @CodesInfo AS TABLE
(
ID BIGINT,
Dept_Code INT,
Product_Code INT ,
Client_Code INT ,
Company_Code INT
)
INSERT INTO @CodesInfo VALUES(5192124001 , '519','21','24','001')
INSERT INTO @CodesInfo VALUES(5182625002 , '518','26','25','002')
INSERT INTO @CodesInfo VALUES(5172326005 , '517','23','26','005')
INSERT INTO @CodesInfo VALUES(5162521007 , '516','25','21','007')
INSERT INTO @CodesInfo VALUES(5152928004 , '515','29','28','004')
INSERT INTO @CodesInfo VALUES(5142829008 , '514','28','29','008')
INSERT INTO @CodesInfo VALUES(5132720021 , '513','27','20','021')
INSERT INTO @CodesInfo VALUES(5192124100 , '519','21','24','100')
INSERT INTO @CodesInfo VALUES(5192124026 , '519','21','24','026')
INSERT INTO @CodesInfo VALUES(5192124023 , '519','21','24','023')
INSERT INTO @CodesInfo VALUES(5192124011 , '519','21','24','011')

--SUPPOSE WE KNOW THE Dept_Code , Product_Code , Client_Code , THEN WE NEED TO FIND THE COMPANY CODE
--i.e LAST RECORD WHICH MATCHED THE ID
SET @Dept_Code = '519'
SET @Product_Code = '21'
SET @Client_Code='24'

/*SELECTING ID OF LAST RECORD THAT MATCHES Concatenation of (Dept_Code + Product_Code + Client_Code) */
SELECT TOP 1 ID FROM @CodesInfo
WHERE CONVERT(VARCHAR,ID) LIKE CONVERT(VARCHAR,@Dept_Code) + CONVERT(VARCHAR,@Product_Code) + CONVERT(VARCHAR,@Client_Code) + '%'
ORDER BY Company_Code DESC
END
GO```

Thanks,
Sailaja.
MyTechnical Blog
• Marked as answer by Bobby-Z on Nov 26, 2012 11:27 PM

Contributor

2838 Points

1120 Posts

### Re: Number Calculations - Concatenations

I am open to any ideas, All the code is really for is coupon-codes for coupons for products/services, as well as for other uses

519111

519=dept, 1 = account type, 1 = promotion (Christmas, Easter, etc...), 1 = new customer only

I was only trying to eliminate a couple of steps in case one of my admins tried to manually enter a coupon code that didn't fit with the scheme.

When adding a new coupon for use in the database several dropdownlists asks for service, promotion, account type, customer type, this is the table that gets checked when creating an invoice, so if I am trying to add a coupon to an invoice that is not for a service that the client is paid for it checks the first three digits of the couponcode for the serviceID, then it checks the clients account type against the coupons account type so a residential client cannot use a coupon made for a commercial client, it also checks for previous invoices that have that serviceID so a returning client cannot get a new customer only coupon.

I suppose I could break all of these up into separate columns and concat them when printing the code on the coupon, however, I am still left with successive ID's for each coupon, my desire would be this

519111001

519111002

519212003

520111001

So each successive service would start its numbering sequence at 001, with an identity column that increments, it would not restart at 001 if the serviceID was different. I would get

5191111

5191112

5192123...

520111100

and the qty of numbers for the code would be different.

So if I emplement separate columns, the second challenge still remains how to detect the last number used for a specific service and increment by 1,

If I use separate columns I would still have to figure out how to read them as one, when inserting them into a combobox for insertion into an invoice.

"Success is the Sum of Small Efforts, Repeated Day in and Day Out - Without Ceasing!"

Currently Learning: ASP, SQL, CSS, JavaScript, AJAX, XML, XSLT, C# So please be patient with me! Thanks

Contributor

3970 Points

1096 Posts

### Re: Number Calculations - Concatenations

#### Bobby-Z

I am open to any ideas, All the code is really for is coupon-codes for coupons for products/services, as well as for other uses

519111

519=dept, 1 = account type, 1 = promotion (Christmas, Easter, etc...), 1 = new customer only

I was only trying to eliminate a couple of steps in case one of my admins tried to manually enter a coupon code that didn't fit with the scheme.

When adding a new coupon for use in the database several dropdownlists asks for service, promotion, account type, customer type, this is the table that gets checked when creating an invoice, so if I am trying to add a coupon to an invoice that is not for a service that the client is paid for it checks the first three digits of the couponcode for the serviceID, then it checks the clients account type against the coupons account type so a residential client cannot use a coupon made for a commercial client, it also checks for previous invoices that have that serviceID so a returning client cannot get a new customer only coupon.

I suppose I could break all of these up into separate columns and concat them when printing the code on the coupon, however, I am still left with successive ID's for each coupon, my desire would be this

519111001

519111002

519212003

520111001

So each successive service would start its numbering sequence at 001, with an identity column that increments, it would not restart at 001 if the serviceID was different. I would get

5191111

5191112

5192123...

520111100

and the qty of numbers for the code would be different.

So if I emplement separate columns, the second challenge still remains how to detect the last number used for a specific service and increment by 1,

If I use separate columns I would still have to figure out how to read them as one, when inserting them into a combobox for insertion into an invoice.

It's a lot easier to concatonate separate columns than it is to parse a single column into it's components.  Separate columns will also make it easier to satisfy any reporting requirements that may arise.

As far as detecting the last number used and adding 1 to it, don't.  First your database can do it better.  Second, simultaneous use may result in duplicate value problems.

As far as the number on your coupon goes, insisting on 2 leading 0's still limits you to 999 numbers.  That could be a huge problem, depending on your business requirements.

• Marked as answer by Bobby-Z on Nov 26, 2012 11:27 PM

Contributor

2838 Points

1120 Posts

### Re: Number Calculations - Concatenations

So I changed my code, instead of using the stored procedure, I created the couponcode in C# and instead of numbering, I used the coupon Value for the final number, this way there will be no duplicates

Thanks for the thoughts! Helped!

"Success is the Sum of Small Efforts, Repeated Day in and Day Out - Without Ceasing!"

Currently Learning: ASP, SQL, CSS, JavaScript, AJAX, XML, XSLT, C# So please be patient with me! Thanks