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
 How to display partial info of a column.PLS HELP!

Author  Topic 

jimdarter
Starting Member

14 Posts

Posted - 2010-05-07 : 15:37:56
Dear All,

I am trying to write a Query where I need to display only the first two values of a coloumn from the SQL table. For ex.

Port Number
3-2
3-1
2-1

The Query should give a following output:
3
3
2

Many Thanks for your help!

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-05-07 : 15:40:46
select
left(port_number,1) --returns 1 sign from port_number
,left(port_number,2) -- returns 2 signs from port_number
from your_table
Go to Top of Page

jimdarter
Starting Member

14 Posts

Posted - 2010-05-07 : 15:47:30
quote:
Originally posted by slimt_slimt

select
left(port_number,1) --returns 1 sign from port_number
,left(port_number,2) -- returns 2 signs from port_number
from your_table



Thanks, what if I want just the last one digit. For ex: The o/p needs to be:
2
1
1
Go to Top of Page

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-07 : 15:52:53

quote:


Thanks, what if I want just the last one digit. For ex: The o/p needs to be:
2
1
1



select
right(port_number,1)
,right(port_number,2)
from your_table
Go to Top of Page

jimdarter
Starting Member

14 Posts

Posted - 2010-05-07 : 15:54:21
quote:

select
right(port_number,1)
,right(port_number,2)
from your_table



Thanks mate but this ain't working for me! :(
Go to Top of Page

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-07 : 15:58:47
You are getting wrong output or any error ?

Sample:

Declare @MyTable table
(Srno int ,
PortNumber varchar(10))

Insert into @MyTable
Select 1,'3-2' union
Select 2,'3-1' union
Select 3,'2-1'


Select Srno,Left(PortNumber,1) LeftVal , Right(PortNumber,1) RightVal from @MyTable
order by Srno

Srno LeftVal RightVal
1 3 2
2 3 1
3 2 1

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.
Go to Top of Page

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-07 : 16:00:18
Is the datatype of PortNumber is Char or varchar ?

In case it is char then use the below statement..

Select Srno,Left(Ltrim(PortNumber),1) LeftVal , Right(Rtrim(PortNumber),1) RightVal from @MyTable
order by Srno

Go to Top of Page

jimdarter
Starting Member

14 Posts

Posted - 2010-05-07 : 16:00:59

This is my complete coloumn in the Table "Inventory"

Port Number
NULL
1-3
2-2
2-3
2-4
2-5
2-6
2-7
2-8
3-1
3-2
3-3
3-4
3-8
6-3
6-6
6-7
6-8

Now When I run
SELECT DISTINCT left([Telica Port],1)
FROM [Inventory_DB].[dbo].[CA]

It gives me a result

NULL
1
2
3
6

Which is completely correct but when I run
SELECT DISTINCT right([Telica Port],1)
FROM [Inventory_DB].[dbo].[CA]

It gives me a result
NULL

which is obviously not correct, any suggestions?


It gives me a result

Go to Top of Page

jimdarter
Starting Member

14 Posts

Posted - 2010-05-07 : 16:04:57
quote:
Originally posted by pk_bohra

Is the datatype of PortNumber is Char or varchar ?

In case it is char then use the below statement..

Select Srno,Left(Ltrim(PortNumber),1) LeftVal , Right(Rtrim(PortNumber),1) RightVal from @MyTable
order by Srno





Thanks mate, that worked :) Appreciate your prompt help.
Go to Top of Page

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-07 : 16:13:11
Try:

Right(rtrim(PortNumber),1)
Go to Top of Page

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-07 : 16:14:38
quote:

Thanks mate, that worked :) Appreciate your prompt help.



You are welcome..

I am here to learn from Masters and help new bees in learning.
Go to Top of Page
   

- Advertisement -