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 |
|
craigwg
Posting Yak Master
154 Posts |
 Posted - 2012-06-12 : 15:00:39
|
| I have a field that is datatype text, and I can't change that.The data SHOULD be numbers, but people are filling in percent signs (%) as well as text like "na". I need for 80 to turn into .80 or 80% to turn into .80, "na" to remain "na".I've done SOME filtering all ready and this works just fine:GMMonth1 = CASE WHEN [17948X147X925GM_M1] like '%[%]%' THEN REPLACE(CAST([17948X147X925GM_M1] AS NVARCHAR),'%','')/(100*1.00) ELSE CAST([17948X147X925GM_M1] AS NVARCHAR)/(100*1.00) END,However this does not take text values like "na" into account. I've attempted it with this:GMTargetG = CASE WHEN [17948X147X925GM_T1] like '%[%]%' THEN REPLACE(CAST([17948X147X925GM_T1] AS NVARCHAR),'%','')/(100*1.00) WHEN [17948X147X925GM_T1] LIKE '[0-9]' THEN CAST([17948X147X925GM_T1] AS NVARCHAR)/(100*1.00) ELSE [17948X147X925GM_T1] END,This is crashing with this error message:Operand type clash: text is incompatible with numericI know this is happening on my LIKE [0-9]. So, what do I do here? ISNUMERIC doesn't work because "na" won't fly that way. Any input?Craig Greenwood |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-06-12 : 15:05:57
|
| why not add a WHERE condition to filter of n/a ones in statement where you do below update?I also didnt understand why one should name columns as [17948X147X925GM_M1],[17948X147X925GM_T1] etc. is it sql generated by some application?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
craigwg
Posting Yak Master
154 Posts |
Posted - 2012-06-12 : 15:10:06
|
| This is not an update. This is a select. Updating the data won't work because data is input constantly. The column names are different because they are two different columns, but same data requirements on both. I just copy and pasted from my query. The format of the column names is a result of working with LimeSurvey, which should be avoided like the plague. But these are the obstacles I'm being met with!Craig Greenwood |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-06-12 : 15:29:36
|
quote:
Originally posted by craigwg
This is not an update. This is a select. Updating the data won't work because data is input constantly. The column names are different because they are two different columns, but same data requirements on both. I just copy and pasted from my query. The format of the column names is a result of working with LimeSurvey, which should be avoided like the plague. But these are the obstacles I'm being met with!Craig Greenwood
ok.if you want to do it select using current case statement you need to cast current return values to varchar as n/a is not numeric value and CASE should always return same datatype through all its condition branches. This will cause an issue if you're using this column further for any numeric manipulation like sorting. If you want to avoid this option would be return value as NULL when n/a and do the change at front end to change them back to n/a for display purpose alone------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
craigwg
Posting Yak Master
154 Posts |
Posted - 2012-06-12 : 15:53:38
|
quote:
If you want to avoid this option would be return value as NULL...
That seems to be the ticket right there. I don't mind removing all non numeric cases to null. Thanks!Craig Greenwood |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-06-12 : 15:57:25
|
| cool. you can use NULLIF for thatNULLIF(column,'n/a') will convert all n/a values to NULL------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
data cleaning