Help with a simple SELECT query

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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with a simple SELECT query

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-08-18 : 16:06:50

Hi All,

I have a Table called Table1 with the following 2 columns (both nvarchar type)

UPC
Brand

The records are like this (all record are seperated by comma);

UPC, Brand
123456789012,OZ
456012102911,LT
4563,EA
7844,2PK

In my single SELECT query I want to get all records from both columns and also get first 5 left characters from the UPC and called it as Manafacturer. I want to keep the Manafacturer field as balnk '' if the UPC length is less than 6 characters.

So the output of my SELECT query should be as follows (all record are seperated by comma);

UPC, Brand,Manafacturer
123456789012,OZ,12345
456012102911,LT,45601
4563,EA
7844,2PK

How can I do this in a single SELECT query.
Please help.

Zee

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-18 : 16:11:15

select a.UPS,a.Band,left(a.UPC,5) as Manufacturer
from
Table1 a

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-08-18 : 16:18:22

Vinnie881,

This will even display the Manafacturer field for UPC where the length of the UPC<=5, which I dont want.
I want to keep the Manafacturer field as balnk '' if the UPC length is less than 6 characters.

Please help.

Thanks,

Zee

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-08-18 : 16:33:18

I modded Vinnie's query for ya:

select a.UPS,a.Band,CASE WHEN LEN(a.UPC) > 5 THEN left(a.UPC,5) ELSE '' END as Manufacturer 
from 
Table1 a

RSQLSERVER
Starting Member

5 Posts

Posted - 2008-08-18 : 16:58:14

Will this work?

select a.UPC,a.Brand,
case
when len(a.UPC) > 6 then LEFT(a.UPC,5)
end
as Manufacturer
from
Table1 a

I may be wrong but lets see...hope it works

? ?????? ??? ?????????? ??? ???? ???????? ????

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-08-18 : 17:15:23

Guys,

I am using the following query;

select UPC, Brand, case when len(UPC) >= 6 THEN left(upc, 6) END Manufacturer
from yourtable

This seem to be working but where the UPC length <=5, it is displaying it as NULL. In that case I want to display it as blank ''.
How can I do this?

Thanks,

Zee

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-08-18 : 17:17:04

perhaps try my query posted on 08/18/2008 : 16:33:18??

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-08-18 : 18:47:40

Lamprey,

Thanks for your help. It worked :)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-08-18 : 19:20:58

No problem! :)