minit - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

minit

Author

Topic

argon007
Starting Member

38 Posts

Posted - 2008-06-07 : 00:38:53

[code]SELECT e.emp_id,
Name =
CASE
WHEN minit IS NOT NULL THEN left (e.fname, 1)+ '.' + minit + '.' + e.lname
WHEN minit IS NULL THEN left (e.fname, 1)+ '.' + minit + e.lname
END,
j.job_id,
j.job_desc
FROM employee e INNER JOIN jobs j ON e.job_id = j.job_id
WHERE e.job_id = 10 OR e.job_id = 12 OR e.job_id = 14
ORDER BY Name[/code]

when i run the command

quote:
A-C71970F A. .Cruz 10 Productions Manager
ENL44273F E.N.Lincoln 14 Designer

how to drop the . if the minint is null?

like A. Cruz not A. .Cruz

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-07 : 03:24:24

[code]SELECT e.emp_id,
left(e.fname, 1) + coalesce('.' + minit, '') + '.' + e.lname as name,
j.job_id,
j.job_desc
FROM employee as e
INNER JOIN jobs as j ON j.job_id = e.job_id
WHERE e.job_id IN (10, 12, 14)
ORDER BY 2[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-07 : 03:47:14

Didnt understand why you want CASE WHEN. You could just do

SELECT e.emp_id,
			Name = 
			COALESCE(left (e.fname, 1)+ '.','') + COALESCE(minit + '.','') + COALESCE(e.lname,''),                           
			j.job_id, 	
			j.job_desc
FROM employee e INNER JOIN jobs j ON e.job_id = j.job_id
WHERE e.job_id = 10 OR e.job_id =  12 OR e.job_id = 14
ORDER BY Name

Also in your CASE you were concatenating to minit even when NULL. it will only yield Name as NULL unless you have concat null yields null set to off.

argon007
Starting Member

38 Posts

Posted - 2008-06-07 : 05:02:27

thanks. but i have tried it, still the same problem.

quote:

A-C71970F A. .Cruz 10 Productions Manager
ENL44273F E.N.Lincoln 14 Designer

i want the A. Cruz, not A. .Cruz

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-07 : 05:11:50

quote:
Originally posted by argon007

thanks. but i have tried it, still the same problem.

quote:

A-C71970F A. .Cruz 10 Productions Manager
ENL44273F E.N.Lincoln 14 Designer

i want the A. Cruz, not A. .Cruz

Whats your current setting for conact ansi nulls yields null?

use this to check

EXEC sp_dboptions 'Yourdbname','concat null yields null'

argon007
Starting Member

38 Posts

Posted - 2008-06-07 : 05:28:04

it is off.

how to turn on?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-07 : 05:52:16

quote:
Originally posted by argon007

it is off.

how to turn on?

EXEC sp_dboptions 'Yourdbname','concat null yields null','TRUE'

argon007
Starting Member

38 Posts

Posted - 2008-06-07 : 06:01:27

set it, but still the same results.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-07 : 09:22:58

quote:
Originally posted by argon007

set it, but still the same results.

Then i guess you dont have a NULL value in minit but it might be a space. Try this too:-

SELECT e.emp_id,
			Name = 
			COALESCE(NULLIF(left (e.fname, 1),' ')+ '.','') + COALESCE(NULLIF(minit,' ') + '.','') + COALESCE(NULLIF(e.lname,' '),''),                           
			j.job_id, 	
			j.job_desc
FROM employee e INNER JOIN jobs j ON e.job_id = j.job_id
WHERE e.job_id = 10 OR e.job_id =  12 OR e.job_id = 14
ORDER BY Name

argon007
Starting Member

38 Posts

Posted - 2008-06-07 : 14:35:02

thank you so much, got solved.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-07 : 14:44:34

quote:
Originally posted by argon007

thank you so much, got solved.

Cool

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources