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.
| Author |
Topic |
|
aakcse
Aged Yak Warrior
570 Posts |
 Posted - 2010-01-21 : 05:57:23
|
| Hi all,I have to remove space from col value, after doing ltrim rtrim, i am still having space in column valuescol1 col2---------------------'0012 ' ' L0012 'col1 is having space at the end, and col2 is having space at both the end. as you can see above I have used '' to show there is space Raw data is some thing like this, which I have loaded into sqlserver2005 using dts'0012 | L0012 |'then I am writing a select ltrim(rtrim('col1'... to insert into new table with out any space at the begin and end.It looks to be a tab and not space in the end and begin of each column, Can you tell me how to remove this tab.Regards,aak |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-21 : 06:08:33
|
| REPLACE(MyColumn, CHAR(9), '')will remove TAB characters (anywhere in the field NOT just at the start/end) |
 |
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2010-01-21 : 06:11:19
|
| thanks Kirsten, what if I only want it to be removed at the begin and end..any way this also helps |
|
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2010-01-21 : 06:31:59
|
| In the same table in one column I have a date data as 'Feb 25 2003 12:00:00:000AM' what conversion can be applied to get this date format? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-21 : 06:39:26
|
| "what if I only want it to be removed at the begin and end"See this query where I answered a question about removing Carriage Return / Line Feed from the end of the column:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138646#541100 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-21 : 06:40:41
|
quote:
Originally posted by aakcse
In the same table in one column I have a date data as 'Feb 25 2003 12:00:00:000AM' what conversion can be applied to get this date format?
Date / Time should be stored in a DATETIME datatype in SQL server.Format it in the front end if at all possible.If you really must format it in SQL then use:CONVERT(varchar(99), MyDateColumn, 999)see SQL Server documentation for CONVERT for the correct values for the "999" parameter, and the correct size for varchar(99) |
|
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2010-01-21 : 07:08:05
|
| Kirsten,when I insert into datetime field it is giving me like this 2003-02-25 00:00:00.000 but actually it is 'Feb 25 2003 12:00:00:000AM'the time factor is getting rounded.. any help in this regard |
|
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2010-01-21 : 07:20:34
|
| hmm I think I should start new thread, as the main question is answered |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-21 : 07:58:05
|
| New thread re: time issue is here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138663 |
|
|
|
|
|
|
|
|
removing space from col value