Trimming last few charecters till it black space

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

Trimming last few charecters till it black space

Author

Topic

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-08-07 : 14:04:24

Hi,

Here is my question.
I want the output with all the characters to be removed starting from the last space.

Below are couple of examples:
Current Value;
ABCDE TenGigabitEthernet8/3 29
CAL00 GigabitEthernet1/9 9
ABCDE TenGigabitEthernet8/3 292
SCFRT GigabitEthernet1/9 9123

I want the output as:Desired Value:
ABCDE TenGigabitEthernet8/3
CAL00 GigabitEthernet1/9
ABCDE TenGigabitEthernet8/3
SCFRT GigabitEthernet1/9

I want the output with all the characters to be removed starting from the last space.

Please advise.

Thank you,
Padhma

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-07 : 14:14:09

Here's one way:


select v
       ,substring(v, 1, len(v)-charindex(' ', reverse(v))) as v_stripped
from   (
       select v = 'ABCDE TenGigabitEthernet8/3 29' union all
       select 'CAL00 GigabitEthernet1/9 9' union all
       select 'ABCDE TenGigabitEthernet8/3 292' union all
       select 'SCFRT GigabitEthernet1/9 9123'
       ) d

OUTPUT:
v                               v_stripped
------------------------------- ------------------------------------------
ABCDE TenGigabitEthernet8/3 29  ABCDE TenGigabitEthernet8/3
CAL00 GigabitEthernet1/9 9      CAL00 GigabitEthernet1/9
ABCDE TenGigabitEthernet8/3 292 ABCDE TenGigabitEthernet8/3
SCFRT GigabitEthernet1/9 9123   SCFRT GigabitEthernet1/9

Be One with the Optimizer
TG

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-08-07 : 14:17:41

Hello TG,

Thank you for your reply. I have data(as given is example) in a column by name InterfaceName and have over 50,000 rows.

So, I am not sure I can use Union there as there are numerous values.
Is there a way to check or the postion of the last space and delete everything after that.
or if you have a suggestion,please advise.

Thank you.
Padhma

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-08-07 : 14:20:42

Please ignore my previous question. It worked as per yousuggestion.
Thank you again TG.

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-08-07 : 14:46:59

Hello TG,

I have another question. I did try to look up for conversion, but nothing worked.

I have Jul 27 2009 12:45AM, but i want the out as:
mm/dd/yyyy hh:mm:ss format.

How would I convert?
Please advise.

Thank you.

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-08-07 : 14:48:24

Also, my current data type for the Jul 27 2009 12:45AM is varhcar(50).
I tried tochange it to datetime. but it will hang since i too much data.

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-08-07 : 14:57:02

This is what I am trying,

SELECT CONVERT(SlotDateTime varchar(100), 110) AS [MM-DD-YYYY HH:MM:SS]
FROM dbo.July_27_31_2009_USC

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-08-07 : 15:05:42

Hi,

I converted the data type.
Now i have it as 2009-07-27 00:00:00.000

and i need to remove all number after the last . PERIOD.

I tried the previous example.
select SlotDateTime,
substring(SlotDateTime, 1, len(SlotDateTime)-charindex('.', reverse(SlotDateTime))) as v_stripped
from TESTTIMESTAMP

being a number, i beleive it wont work. how should i remove for datetime type the last number after the last '.'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-07 : 15:12:49

Where are you presenting this data? Some application front-end or perhaps a report-writer? If so you should return it as a DATETIME value and use the front-end to format the date to whatever you need. Most applications have robust formatting abilities.

And if you haven't figured it out yet you should store dates in no other datatype other than DATETIME or SMALLDATETIME.

Be One with the Optimizer
TG

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-08-07 : 15:16:17

Hello TG,

I am using it back end purpose where the server will accept timestamp in:
MM/dd/yyyy HH:mm:ss, for example 07/20/2005 19:30:00 ONLY.

Now i have it as 2009-07-27 00:00:00.000. which has to be converted to the desired output as above.

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-08-07 : 15:47:50

TG or anyone else, can you please suggest a way out here.

Thank you.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-07 : 15:51:00

>>Now i have it as 2009-07-27 00:00:00.000. which has to be converted to the desired output as above.

The output is what I am asking about. What is using the output? A front-end application? web server? crystal reports? Or will people actually be looking at this data in a Management Studio Query window?

To convert a DATETIME to this string format: MM-DD-YYYY HH:MM:SS
you can use a combination of CONVERTs

select convert(varchar, getdate(), 105) + ' ' + convert(varchar, getdate(), 108)
output:
07-08-2009 15:44:57

So you can try this:
select convert(varchar, convert(datetime, SlotDateTime), 105) + ' ' + convert(varchar, convert(datetime, SlotDateTime), 108)
from TESTTIMESTAMP

Be One with the Optimizer
TG

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-08-07 : 15:58:59

Dear TG,

I need to feed in data to the server which will not be used by any user. It will be displayed on a front end-on a client IE browser to view and the main purpose is the use this to analyze or simulate model on a 5 min interval basis. its for modeling based on time. hence the time stamp format is critical as the server accepts the feed only if its on the mm/dd/yyyy hh:mm:ss format, else it will simple reject the input file.

I tried the the conversion you told me, it work. just that instead of 105 i changed the format to 101.

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources