March 21, 2011 at 7:17 pm
Hi,
Here is my sample data, In my source table i have more than 60k rows.
Date
2/10/2006
2/10/06
2/10/06
Thursday, 2/16/2006
Thursday, 2/16/2006
Thursday, 2/16/2006
Thursday, 2/16/2006
Thursday, 2/16/2006
Thursday, 2/16/2006
3/9/2006
3/9/2006
3/15/2006
7/7/06
7/7/06
How i can delete all Alpha values from my source data e.g I want to delete "Thursday"
Thanks in advance
March 21, 2011 at 7:42 pm
Try the ISDATE function.. something like:
delete from table where isdate(column) <> 1
March 21, 2011 at 8:02 pm
If everything you want to remove always ends with a comma you could use something like this:
SELECT
[Date],
LTRIM(SUBSTRING([Date], CHARINDEX(',', [Date]) + 1, DATALENGTH([Date]))) AS DateNoComma
FROM dbo.Table
WHERE
CHARINDEX(',', [Date]) > 0
If that returns what you want, you can just turn it into an UPDATE query to fix your data.
March 21, 2011 at 8:03 pm
Thanks for your prompt reply, Here is the error that i am getting after i try syntax that you advised,
Msg 4145, Level 15, State 1, Line 2
An expression of non-boolean type specified in a context where a condition is expected, near ';'.
March 21, 2011 at 8:10 pm
SELECT
[Date],
LTRIM(SUBSTRING([Date], CHARINDEX(',', [Date]) + 1, DATALENGTH([Date]))) AS DateNoComma
FROM [DELETE]
WHERE
CHARINDEX(',', [Date]) > 0
I am getting nothing, Both column are blank.
March 21, 2011 at 8:21 pm
I tested and it works with the sample table I created, but since you didn't provide DDL or an actual data sample I can't test with what you have.
For better, quicker answers on T-SQL questions, click on the following: http://www.sqlservercentral.com/articles/Best+Practices/61537/
BTW using reserved words for column and table names isn't a very good practice. (Using DATE and DELETE)
March 22, 2011 at 7:20 pm
I just want to give you update. I really appreciate your help. It works fine. I tried on my office database its works. Thanks for your time. Problem Solved.
March 23, 2011 at 1:24 am
Your welcome and thanks for letting us know it work for you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply