increment string in vchar column

Question

Post reply

increment string in vchar column

a.borgeld

Ten Centuries

Points: 1395
More actions
September 13, 2010 at 6:20 am

#223821

We have a column called StafNr with known and unknown numbers, because only people who have a permanent contract
get a StafNr. The stafnumber comes with an update from the staf primairy system or will be input by hand if a join can't be made.
My idea is to give everybody at first an unknown number and then see if it stays this way or it will be changed with a permanent contract.
To give everybody the unknow stafNr i must increment Unknown_200to Unknown _201. How do i do that in SQL? Its a string, so there we got a problem.
There is a LEFT function, but it has to have a value (1,2 or 3); but now i work with 3 positions and maybe i'm gonna work with 4.
I need it in a update and in a insert statement.
So as an example see the attacht image.

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Will this suffice?

DECLARE @StafNr VARCHAR(15)

SET @StafNr = 'Unknown_201'

SELECT CAST(SUBSTRING(@StafNr,CHARINDEX('_', @StafNr,1)+1,3)AS INT) + 1

Result is 202

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

WayneS SSC Guru Points: 95460 More actions · Answer 2

This should work for you:

declare @test-2 table (Staff_nbr varchar(20));

insert into @test-2

select 'Unknown_200'

-- un-remark the following line to work with 4 digit numbers

--UNION ALL select 'Unknown_2000'

;with CTE (staff_nbr) AS

(

select convert(int, substring(Staff_nbr, charindex('_', Staff_nbr)+1, 20))

from @test-2

)

select 'Unknown_' + convert(varchar(10), max(staff_nbr)+1)

from CTE;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

a.borgeld Ten Centuries Points: 1395 More actions · Answer 3

Thanks guys, i'm gonna test a bit for the best solution.

WayneS SSC Guru Points: 95460 More actions · Answer 4

a.borgeld (9/13/2010)
Thanks guys, i'm gonna test a bit for the best solution.

... which is always a good idea for any code you get off the internet.

Please respond back and let us know how you resolved it... and if you have any other questions.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

a.borgeld Ten Centuries Points: 1395 More actions · Answer 5

Hi Wayne,

Your right about that.

Mostly i report back, i'm new, but its nice to close with the final result.

Kind regards,

André

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 6

You should split the alpha and numeric for storage. Don't fall into the trap of feeling you have to store what is displayed / entered.

So, create two physical columns, one for the varchar portion of the value and one for the numeric. Then create a computed column that calculates the display value.

For example:

CREATE TABLE #test1 (

c1 varchar(30),

c2 int,

c3 AS c1 + '_' + CAST(c2 AS varchar(10))

)

INSERT INTO #test1 VALUES('Unknown', 200)

SELECT c1, c2, c3

FROM #test1

Once the alpha and numeric are split, it's easy to increment the numeric value.

For the existing table, you could create new column names and make the existing column the computed column ... except for any place where the value is INSERTed / UPDATEd. INSERTs / UPDATEs would have to done against the physical columns, of course, not the computed column.

Scott Pletcher, SQL Server MVP 2008-2010

WayneS SSC Guru Points: 95460 More actions · Answer 7

@scott - excellent point (wish I had made it!)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

a.borgeld Ten Centuries Points: 1395 More actions · Answer 8

I need the output in *.csv but i can concatenate the output.

Good idea to, i tried something else with a loop, come back on it today.

Thanks guys.

WayneS SSC Guru Points: 95460 More actions · Answer 9

a.borgeld (9/13/2010)
I need the output in *.csv but i can concatenate the output.
Good idea to, i tried something else with a loop, come back on it today.
Thanks guys.

The fastest way to make a csv is to use the FOR XML PATH('') clause. Ask if you need assistance.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

a.borgeld Ten Centuries Points: 1395 More actions · Answer 10

I use BCP now with a view with headers via a select, its looks very good.

But its intereseting to know more options.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

a.borgeld (9/13/2010)
I need the output in *.csv but i can concatenate the output.
Good idea to, i tried something else with a loop, come back on it today.
Thanks guys.

BCP can create a comma delimited file super fast if it needs to be a file. If it's a script you run, change the type of output of the results window... that'll work just fine too.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

a.borgeld Ten Centuries Points: 1395 More actions · Answer 12

BCP is very cool.

Guys with your information and information from Ray Wong i created a function with a loop.

This works.

But i told you about the table who needs to update the Stafnr column, increment it.

The case now is that maybe i get 2 stafmembers a day.

The update wants to update Unknown_200 to Unknow_201, but now there are two records empty so he tries to update the 2 new staff members with Unknown_201. While i want to get a Unknow_201 and after that a Unknown_202, you get two Unknown_201's.

Has anybody got any solution for this.

WayneS SSC Guru Points: 95460 More actions · Answer 13

a.borgeld (9/14/2010)
BCP is very cool.
Guys with your information and information from Ray Wong i created a function with a loop.
This works.
But i told you about the table who needs to update the Stafnr column, increment it.
The case now is that maybe i get 2 stafmembers a day.
The update wants to update Unknown_200 to Unknow_201, but now there are two records empty so he tries to update the 2 new staff members with Unknown_201. While i want to get a Unknow_201 and after that a Unknown_202, you get two Unknown_201's.
Has anybody got any solution for this.

Sure, just modify what I did earlier:

-- test data

DECLARE @test-2 TABLE (RowID INT IDENTITY, Staff_nbr varchar(20));

INSERT INTO @test-2

SELECT '' UNION ALL -- will need to be updated with the next number

SELECT 'Unknown_200'

-- un-remark the following line to work with 4 digit numbers

--UNION ALL SELECT 'Unknown_2000'

-- declare variable to store the current max value

DECLARE @max-2 INT;

-- get the current max value

;WITH CTE (staff_nbr) AS

(

SELECT convert(int, substring(Staff_nbr, charindex('_', Staff_nbr)+1, 20))

FROM @test-2

)

SELECT @max-2 = max(staff_nbr)

FROM CTE;

WITH CTE AS

( -- get just the rows with no staff_nbr.

-- also get an ascending row_number

SELECT RowID,

Staff_Nbr,

RN = ROW_NUMBER() OVER (ORDER BY RowID)

FROM @test-2

WHERE Staff_nbr = ''

)

-- update those missing rows with the max + row number

UPDATE CTE

SET Staff_nbr = 'Unknown_' + CONVERT(varchar(10), @max-2 + RN);

-- show what we've got

SELECT *

FROM @test-2;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

a.borgeld Ten Centuries Points: 1395 More actions · Answer 14

In one word. Thank you for the lesson Wayne.

When its al finished i will come back to this topic.

Kind regards,

André