How to get the number minus the first digit? - 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

General SQL Server Forums

New to SQL Server Programming

How to get the number minus the first digit?

Author

Topic

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-04-03 : 05:50:10

Hi all,

I got a documentNo in the format 'A000001'

I want to get the number only and ignore the 1st char (don't care if it is A, Z, 1 etc) so that I can increment it by 1 to get the next number in the series. P.s. I did not design the database. :(

How do I do this? Note that the length of the documentNo is not always A + 6 digits. It can be 5 digits, or 4 digits, or 7 digits etc. :( So I am lost when using substring or right (how many digits)?

Is there any regular expressions in mssql to get the documentNo so that I can increment it by 1?

any help is very much appreciated.

Regards,

Eugene

check out my blog at http://www.aquariumlore.blogspot.com

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-04-03 : 06:00:48

--remove first character for any string length
declare @C varchar(50)
set @C='A000001'

--Select Reverse(Left(Reverse(@C),len(@C)-1))
--sorry more straight forward:
Select Right (@C,len(@C)-1)

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-04-03 : 06:26:10

try this once,

select substring(col,2,len(col))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-03 : 06:31:24

or

select stuff(@C,1,1,'')

Madhivanan

Failing to plan is Planning to fail

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-04-03 : 07:38:16

I tested speed of the 3 ways and found no performance difference over 100,000 loops so I guess it's just down to personal preference:
Select Right (@C,len(@C)-1)
select substring(@C,2,len(@C))
select stuff(@C,1,1,'')

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-03 : 09:34:39

Loops? why in the world would you loop? Do it SET BASED.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-04-03 : 09:42:28

To test which was slowest !!

I wanted to see if I could squeeze any difference between the 3 methods because there was not a single nanosecond between them when ran once.

why not question why I ran it 100,000 times - it would have been quicker to run it just once - but then that would defeat the purpose of the test.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-04-03 : 10:18:02

If there are multiple users you will need to make sure that documentNo is unique:

DECLARE @series char(1)
SET @series = 'A'

INSERT INTO YourTable (documentNo, ...)
SELECT @series + REPLACE(STR(CAST(SUBSTRING(LastNo, 2, 20) AS int) + 1, LEN(LastNo) - 1), ' ', '0')
    ,...
FROM
(
    SELECT MAX(documentNo) AS LastNo
    FROM YourTable WITH (UPDLOCK, HOLDLOCK)
    WHERE documentNo LIKE @series + '%'
) D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-03 : 12:46:31

quote:
Originally posted by EugeneLim11

Hi all,

I got a documentNo in the format 'A000001'

I want to get the number only and ignore the 1st char (don't care if it is A, Z, 1 etc) so that I can increment it by 1 to get the next number in the series. P.s. I did not design the database. :(

How do I do this? Note that the length of the documentNo is not always A + 6 digits. It can be 5 digits, or 4 digits, or 7 digits etc. :( So I am lost when using substring or right (how many digits)?

Is there any regular expressions in mssql to get the documentNo so that I can increment it by 1?

any help is very much appreciated.

Regards,

Eugene

check out my blog at http://www.aquariumlore.blogspot.com

why dont you create an identity column and then make DocumentNo computed based on it. something like

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-04-05 : 22:31:45

thank you

check out my blog at http://www.aquariumlore.blogspot.com

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-04-05 : 22:33:39

DarkDusky, your reply is very interesting :)

Regards,

Eugene

check out my blog at http://www.aquariumlore.blogspot.com

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-04-05 : 22:40:14

Ifor,

you just raised a very important issues - dealing with concurrency...

Concurrent requests or bulk requests can be a nightmare. Your sql query seems to hold and lock the database. what happens if there are concurrent request? Will there ever be a deadlock? how do I resolve this deadlock?

Eugene

check out my blog at http://www.aquariumlore.blogspot.com

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-04-06 : 04:43:55

Eugene - are you getting paid for hits on your aquarium site? What have fish got to do with SQL?

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-04-06 : 05:50:06

No. It is my my signature... For some reasons, the sql forums keeps including my signature without me knowing. It is not visible in the message until I hit the submit reply button. Apologise for the error.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-06 : 06:08:43

quote:
Originally posted by EugeneLim11

No. It is my my signature... For some reasons, the sql forums keeps including my signature without me knowing. It is not visible in the message until I hit the submit reply button. Apologise for the error.

If you want to remove it, goto your profile and remove

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources