sql2k: increment numeric part in arbitrary string

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

Script Library

sql2k: increment numeric part in arbitrary string

Author

Topic

daniel_nebel111
Starting Member

2 Posts

Posted - 2003-04-11 : 15:24:57


CREATE FUNCTION fctisnumericex(@c varchar(1))  
RETURNS int AS  
BEGIN 
  RETURN CASE WHEN ASCII(@c)>=ASCII('0') AND ASCII(@c)<=ASCII('9') THEN 1 ELSE 0 END END

CREATE FUNCTION fctstringincrement (@string varchar(255),@maxlen int)  
RETURNS varchar(255) AS  
BEGIN 
  DECLARE @@posr int
  DECLARE @@posl int
  DECLARE @@c varchar(1)
  DECLARE @@token1 varchar(255)
  DECLARE @@token varchar(255)
  DECLARE @@token3 varchar(255)
  DECLARE @@i int
  /* emulates parts of the behaviour of s_modformatting::substringincrement */
  /* 1. find the place where the numeric token starts from the right */
  /* if we didn't find any non-numeric part then it might well be that the rightmost digit is already numeric */
  IF dbo.fctisnumericex(SUBSTRING(@string,DATALENGTH(@string),1))=1
BEGIN 
    SELECT @@posr=DATALENGTH(@string) 
  END ELSE BEGIN
    SELECT @@i=DATALENGTH(@string)
    SELECT @@c=SUBSTRING(@string,@@i,1)
    WHILE dbo.fctisnumericex(@@c)!=1 BEGIN
      SELECT @@i=@@i-1
      IF @@i<1 BEGIN BREAK END
      SELECT @@c=SUBSTRING(@string,@@i,1)
    END
    SELECT @@posr=@@i
  END
  /* so have we got any numeric part inside that string? */
  IF @@posr>0 BEGIN
    /* yep. see how long it lasts */
    SELECT @@i=@@posr
    SELECT @@c=SUBSTRING(@string,@@i,1)
    WHILE dbo.fctisnumericex(@@c)=1 BEGIN
      SELECT @@posl=@@i
      SELECT @@i=@@i-1
      IF @@i<1 BEGIN BREAK END
      SELECT @@c=SUBSTRING(@string,@@i,1)
    END
    /* separate now the parts of the string */
    IF @@posl>1 BEGIN SELECT @@token1=SUBSTRING(@string,1,@@posl-1) END ELSE BEGIN SELECT @@token1='' END
    SELECT @@token=SUBSTRING(@string,@@posl,@@posr-@@posl+1)
    IF @@posr<DATALENGTH(@string) BEGIN SELECT
@@token3=SUBSTRING(@string,@@posr+1,DATALENGTH(@string)-@@posr) END ELSE BEGIN SELECT @@token3='' END
    /* increment the numeric part */
    SELECT @@token=convert(varchar(255),convert(int,@@token)+1)
  END ELSE BEGIN
    /* no numeric part at all. start with 1 at the end */
    SELECT @@token1=@string
    SELECT @@token='1'
    SELECT @@token3=''
  END
  /* recompose the string and trim to max length if necessary */
  RETURN SUBSTRING(@@token1+@@token+@@token3,1,@maxlen)
END

daniel_nebel111
Starting Member

2 Posts

Posted - 2003-04-11 : 15:30:11

sorry; i usually DO use indentation but either the copy or the paste or the forum code must have spoilt it

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2003-04-11 : 16:46:05

If you use [ CODE ] tags around it (except without the spaces), you'll get your indentation. FYI - I modified your post.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-11 : 16:48:04

I've never seen:

DECLARE @@Variable ...

What does the @@ do? is it different from @? I always thought it was for "system" variables only...

but it does seem to work, i just tested it?

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-14 : 15:00:31

BOL:

global variable
In SQL Server, a variable that can be referenced by multiple Data Transformation Services (DTS) tasks. In earlier versions of SQL Server, the term referred to the Transact-SQL system functions whose names start with two at signs (@@).

It was my (seriously flawed) assumption, that a global variable, like a global table would remain in existance as long as it's being referenced. But, how does one process (sproc) refer to it a global variable that is created by another?

Anyway, the following does work:

declare @@text varchar(255)
Select @@text = '123'
Select @@Text

Oh and Graz...thanks much about the [ code ] tags. Been driving crazy (it's not a drive but a putt) since I've been comming here.


	THANKS!!

Brett

8-)

Edited by - x002548 on 04/14/2003 15:01:36

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources