0

Want to have a counter field that starts with a character followed by a number as a regular counter, but the counter should start with x number of zeros.

Example:

L00001
L00002
L00003

Is it possible to construct such counters in sql server?

Update:

It should be auto generated counter values that is incremented by 1 when a new row is added. The number of rows in the table is never going to be as large as L99999.

Its Sql server 2012 express im using.

MikeAlike234
  • 759
  • 2
  • 12
  • 29

5 Answers5

2

One possible trick, use a computed column:

--DROP TABLE MyTable
CREATE TABLE MyTable
 (
   MyTableId   int  not null  identity(1,1)
  ,MyTableKey as 'L' + right('00000' + cast(MyTableId as varchar(5)), 5)
    persisted
    constraint PK_MyTable
     primary key clustered
  ,SomeData    varchar(50)
 )

Column MyTableKey is calculated based on the identity column and actually stored within the table (persisted, as opposed to calculated on the fly--so it's only calculated when a row is first inserted). This allows it to be indexed, and I set it as the primary key sonce nothing should ever reference the "Id" column.

Some sample data:

INSERT MyTable (SomeData) values 
  ('First')
 ,('Second')
 ,('Third')
 ,('Etc')

SELECT *
 from MyTable
Philip Kelley
  • 39,426
  • 11
  • 57
  • 92
  • I think `right('00000' + cast(MyTableId as varchar(5)), 6)` should actually be `right(..., 5)` for his case, but in any case, this is a nice solution. +1 – Manny Feb 14 '14 at 15:38
  • Whoops, I missed that -- originally had the "L" inside the `right` statement, and didn't adjust the length when that proved a mistake. The answer has been adjusted. – Philip Kelley Feb 14 '14 at 16:53
0

Assuming SQL Server, this may be what you're looking for as far as the counter goes (not sure if you need that too):

ROW_NUMBER ( ) 
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

http://technet.microsoft.com/en-us/library/ms186734.aspx

For example,

SELECT
    SomeColumn,
    ROW_NUMBER() OVER (ORDER BY SomeColumn) AS Number
FROM
YourTable

As far as padding a value with zeroes, you can do something like:

SELECT 
    'L' + REPLICATE('0', 5 - LEN(YourId)) +
        CONVERT(varchar(255), YourId)...

REPLICATE repeats the specified value N times, N being the number you provided. And if it's a potential scenario, you should also consider what will happen if the length of YourId exceeds 5 (your return value would be NULL).

http://technet.microsoft.com/en-us/library/ms174383.aspx

Manny
  • 967
  • 1
  • 6
  • 17
  • Since, according to your update, you need to do this at row insert, this solution no longer applies. Philip Kelley's answer addresses what you need. – Manny Feb 14 '14 at 15:36
0

i think you need to create a function which return varchar and no input require

inside function get the max value from table remove @i from it such that you have only int left and store in @max increment by one

Declare @i char(1)='L'
Declare @max int=1112 

select @i+replicate('0',4-len(@max)+1)+casT(@max as varchar)
KumarHarsh
  • 5,046
  • 1
  • 18
  • 22
0

You can also try with STUFF function:

DECLARE @Number INT = 123
SELECT 'L' + STUFF('00000', 5-LEN(@Number)+1, LEN(@Number) , @Number)
Kenan Zahirovic
  • 1,587
  • 14
  • 24
  • OK, you need autogenerated counter... Please see at: [link](http://stackoverflow.com/questions/8170950/how-do-i-add-string-with-auto-increment-value-in-sql-server) , user @marc_s has a solution for you. – Kenan Zahirovic Feb 14 '14 at 15:10
0

hi david use this query for create your table

CREATE TABLE [dbo].[Emp](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [Name] [varchar](50) ,
        [AutoGeneratedColumn]  AS ('L'+right(replicate('0',(5))+CONVERT([varchar](5),[ID],0),(5)))
    ) ON [PRIMARY]
Seymour
  • 7,043
  • 12
  • 44
  • 51
Heena Chhatrala
  • 242
  • 1
  • 8