HELP --- SQL Programming

Question

HELP --- SQL Programming

Viewing 8 posts - 46 through 52 (of 52 total)

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

kipp SSChasing Mays Points: 606 More actions · Answer 1

Hi Bob, can I ask you a quick question....

I have some data in a table that during a conversion we put in all lower and in all upper case. This is a name column in the table so it need to be Firstname M. Lastname, or just Firstname or Firstname Lastname.

Is there anyway to convert that in SQL Server?

Thanks in Advance..

Kipp

Bob Fazio SSChampion Points: 10794 More actions · Answer 2

DECLARE @bob-2 VARCHAR(200)

SELECT @bob-2 = 'robert'

SELECT UPPER(SUBSTRING(@bob,1,1)) + LOWER(SUBSTRING(@bob,2,LEN(@bob)))

SELECT @bob-2 = 'ROBERT'

SELECT UPPER(SUBSTRING(@bob,1,1)) + LOWER(SUBSTRING(@bob,2,LEN(@bob)))

kipp SSChasing Mays Points: 606 More actions · Answer 3

Hello Bob, I am stuck again... I posted the below and wondered if you had any solution...

I have the below data in a table called say "kipp".

I am trying to get a script to work that will look at the first proj_id (which is the 5 digit proj_id, 11293), if that base id has a desc, then fill that desc in on the below proj_id's that belong to the group (ie. 11293.000,11293.000.00...) and so on... I cant seem to figure it out as a newbie to SQL Server. Any Help would be greatly appreciated.

Thanks!!

proj_id desc

------------------------------ ------------------------------

11293 Description1

11293.000

11293.000.00

11293.001

11293.001.01

11293.001.02

11294 Description2

11294.000

11294.000.00

11294.001

11294.001.01

11294.001.02

Bob Fazio SSChampion Points: 10794 More actions · Answer 4

The issue here is a bad design.

Basically what you are calling the top level project should be a table of it's own.

In fact you should have at least 3 tables where you have 1 right now. At the very least, what I would suggest is splitting the PK from 99999.999.9999 into 3 columns.

You can create a computed column to replace the old one by for compatiblity.

This change will simplify ALL of the issues in this thread.

kipp SSChasing Mays Points: 606 More actions · Answer 5

hey thanks for the quick reply.

that sounds good, although I do not ahve access to change or add anything to the tables.

this is an existing table that is and has been used for a long time.

I just need to figure out a script that can check that column and populate it with the main description.

Bob Fazio SSChampion Points: 10794 More actions · Answer 6

BEGIN TRANSACTION

;WITH tblDesc AS (

SELECT proj_id,

[desc] FROM tblTest

WHERE proj_id NOT LIKE '%.%'

)

UPDATE tblTest

SET [desc] = tblDesc.[desc]

FROM tblTest

INNER JOIN tblDesc

ON SUBSTRING(tblTest.proj_id,1,LEN(tblDesc.proj_id)) = tblDesc.proj_id

WHERE tblTest.proj_id LIKE '%.%' AND tblTest.[desc] IS NULL

SELECT * FROM tblTest

ROLLBACK TRANSACTION

This should do what you want. FYI, this is not efficient. Definately run on a test system first.

SQL_ABD SSCertifiable Points: 5832 More actions · Answer 7

Hi,

Just a point,

use charindex('.',proj_id) = 0 instead off not like '%.%'

and charindex('.',tblTest.proj_id) > 0 instead off tblTest.proj_id LIKE '%.%' for best performance.

Kindly,

Ahmed

R. Brush SSCommitted Points: 1669 More actions · Answer 8

Another possibility:

UPDATE kipp

SET descr = ISNULL(b.[descr],'') AS [descr]

FROM kipp a

LEFT JOIN(SELECT proj_id, descr

FROM kipp

WHERE len(proj_id) = 5 and isnull(descr,'')<>''

) b

ON SUBSTRING(a.proj_id,1,5) = b.proj_id