Computed column with leading static number

Question

Post reply

Computed column with leading static number

nilknarf

Right there with Babe

Points: 743
More actions
June 17, 2016 at 6:08 am

#308535

static number (12345) + ID number

Viewing 10 posts - 1 through 9 (of 9 total)

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

Phil Parkin SSC Guru Points: 246970 More actions · Answer 1

franklinkeith (6/17/2016)
static number (12345) + ID number

This is somewhat terse – what is your question, exactly?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Lowell SSC Guru Points: 323518 More actions · Answer 2

franklinkeith (6/17/2016)
static number (12345) + ID number

SELECT 12345[highlight="#ffff11"]00000[/highlight] + ID, so as long as your Id is less than 99999, it fits your criteria.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

nilknarf Right there with Babe Points: 743 More actions · Answer 3

Trying create a column with leading code 12345 along with id number. 12345 999999999.

nilknarf Right there with Babe Points: 743 More actions · Answer 4

what if my id is 9 digits? and the code is 5 digits? 12345 999999999

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

nilknarf (6/17/2016)
static number (12345) + ID number

What does the 12345 represent?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

nilknarf Right there with Babe Points: 743 More actions · Answer 6

I'm trying to add the formula in column properties 12345 ' ', idnumber

I get this error saving

'IDCARD' table

- Unable to modify table.

Incorrect syntax near ' '.

any help is greatly appreciated just a beginner.. :-):-)

Lowell SSC Guru Points: 323518 More actions · Answer 7

nilknarf (6/17/2016)
Trying create a column with leading code 12345 along with id number. 12345 999999999.

so, a string that happens to contain numbers then, since you included a space?

i would use an identity field, and a persisted calculated column.

CREATE TABLE MYTable (ID int identity(1,1) not null primary key, Sometext varchar(30))

ALTER TABLE MYTABLE ADD

CompanyID AS '12345 ' + RIGHT('000000000' + CONVERT(VARCHAR,ID),9) PERSISTED

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ScottPletcher SSC Guru Points: 101109 More actions · Answer 8

Or:

id bigint IDENTITY(12345000000000, 1) NOT NULL,

...

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Sergiy SSC Guru Points: 110208 More actions · Answer 9

'12345' + replace(STR(ID,9,0), ' ', '0')

_____________
Code for TallyGenerator