Help needed

Question

Post reply

Help needed

Best-330874

SSCrazy

Points: 2350
More actions
January 2, 2007 at 2:32 pm

#122209

i have a table Called GE_Trans which has a column TreatyNumber which has values
like below
Sample data
TreatyNumber
MRQYP2A2
MRQYP3A1
MRQYS2A2
i want to add a new field to this table called TreatyID with datatype bigint
and write in this field either 2 OR 3 as it is depends on the 6th Digit in
the TreatyNumber field.
what i mean by this is for instance by looking at te sample data in the first instance
TreatyNumber is MRQYP2A2 (6th Digit in the TreatyNumber field is 2 which i want to write in my new field TreatyID)

Viewing 14 posts - 1 through 13 (of 13 total)

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

Sergiy SSC Guru Points: 110206 More actions · Answer 1

So, what is your problem?

You don't know how to use SUBSTRING?

_____________
Code for TallyGenerator

Best-330874 SSCrazy Points: 2350 More actions · Answer 2

i know how to use it but i dont know how to use it in this case

can you guide me please

mrpolecat SSCertifiable Points: 6964 More actions · Answer 3

When you create your new field you can use substring(treatynumber,6,1) as a formula value.

Sergei Zarembo Hall of Fame Points: 3887 More actions · Answer 4

from BOL:

SELECT SUBTRING(FirstName,1,1) AS Initial, LastNameFROM Employees

This is the result set:

Initial..........LastName-------------------------A................FunkM................PearsonL................CalafatoN................DannerJ................LeeS................ByhamM................SutterR................KingA................Doyle

Sergei Zarembo Hall of Fame Points: 3887 More actions · Answer 5

guys, sorry for messing up the formatting. will be more careful next time.

Best-330874 SSCrazy Points: 2350 More actions · Answer 6

Can you write the actual query to show me becz i am still not cleared fully.

Thanks

mrpolecat SSCertifiable Points: 6964 More actions · Answer 7

do you want a query to select the data or one to update the data or one to have the field always contain the data?

Sergiy SSC Guru Points: 110206 More actions · Answer 8

TreatyID = substring(TreatyNumber, 6, 1)

is an actual query.

_____________
Code for TallyGenerator

Best-330874 SSCrazy Points: 2350 More actions · Answer 9

Best-330874

SSCrazy

Points: 2350

January 2, 2007 at 3:13 pm

#680850

update the data

mrpolecat SSCertifiable Points: 6964 More actions · Answer 10

update GE_Trans set TreatyID = substring(TreatyNumber, 6, 1)

run this after you add your new field to the table. Also, I don't see why you would want to use a bigint datatype when you are only going to hold a value between 0 and 9

John Rowan SSC Guru Points: 56440 More actions · Answer 11

Also keep in mind that what you've asked for will only set the TreatyID values for existing rows. You will need to also take care of populating the TreatyID for new rows inserted into your table.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 12

John Rowan is correct about the existing rows... that's why some have suggested that the new column be a "calculated column" (see CREATE TABLE and ALTER TABLE in Books Online for how to make one). Creating a "real" column for this would actually be a violation of 3rd normal form because you are duplicating data in columns....

--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)

Best-330874 SSCrazy Points: 2350 More actions · Answer 13

Best-330874

SSCrazy

Points: 2350

January 2, 2007 at 9:05 pm

#680892

Thanks for the help to all of you