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 Number3-23-12-1The Query should give a following output: 332Many 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_numberfrom your_table |
|
|
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_numberfrom your_table
Thanks, what if I want just the last one digit. For ex: The o/p needs to be:211 |
|
|
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:211
select right(port_number,1) ,right(port_number,2) from your_table |
|
|
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! :( |
|
|
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' unionSelect 2,'3-1' unionSelect 3,'2-1' Select Srno,Left(PortNumber,1) LeftVal , Right(PortNumber,1) RightVal from @MyTableorder by SrnoSrno LeftVal RightVal1 3 22 3 13 2 1Regards,BohraI am here to learn from Masters and help new bees in learning. |
|
|
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 @MyTableorder by Srno |
|
|
jimdarter
Starting Member
14 Posts |
Posted - 2010-05-07 : 16:00:59
|
This is my complete coloumn in the Table "Inventory"Port Number NULL1-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 NULL1236Which is completely correct but when I run SELECT DISTINCT right([Telica Port],1) FROM [Inventory_DB].[dbo].[CA]It gives me a result NULLwhich is obviously not correct, any suggestions? It gives me a result |
|
|
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 @MyTableorder by Srno
Thanks mate, that worked :) Appreciate your prompt help. |
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2010-05-07 : 16:13:11
|
Try:Right(rtrim(PortNumber),1) |
|
|
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. |
|
|
|