0

Ok, I have a table I am querying for an outside project. The table has a field that is alphanumeric but the external project can only support numeric data. The outside project is needing my data as an Excel spreadsheet so I am trying to just export my query to Excel which I can do but I can't seem to figure out if there is a simple convert feature to do this for me. The originating table has the field as VARCHAR(10). I tried CAST and CONVERT but no go. This is SQL Server 2012.

Thanks.

Jayson
  • 73
  • 2
  • 2
  • 9
  • what was the error when you convert it? – RoMEoMusTDiE Jun 12 '17 at 01:58
  • "_I tried CAST and CONVERT but no go._" For crying in a bucket, what's so difficult about showing what you actually tried and telling exactly what went wrong: either an error message received, or expectation not met. ... The point is there are at least 2 possible things that went wrong. If you just mucked up the syntax - that's easy, but fixing it requires knowing what you did wrong. If you have data that cannot be converted to numeric, we need to know _what those value are_ and what _you expect_ to happen for such values. – Disillusioned Jun 12 '17 at 02:16
  • I apologize for not throwing my code in here. So, here is the SQL statement I did: Select Cast(MeetingID as INT) as MeetingID From Table Error was: Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value 'ADAPR2014' to data type int. – Jayson Jun 12 '17 at 02:18
  • 2
    looking at that.. do you think it will convert into int? – RoMEoMusTDiE Jun 12 '17 at 03:17
  • 1
    That is my question. If I have an alphanumeric value for a record, can I convert it to all numeric. – Jayson Jun 12 '17 at 11:57
  • @Jayson Stop. Pause. Step back from your fixation on your current problem and think about: _What should the alphanumeric value `apples` become when converted to numeric?_ When you understand the implications of what it means to just "simply" convert alphanumeric to numeric, you can either abandon your question, or [edit] it into something that makes sense. ***PS: Please re-read my first comment in full.** You still have not responded to everything in that comment.* – Disillusioned Jun 13 '17 at 00:20
  • @craig young. Not sure what you mean. I shared the query I wrote and the error message and what I am looking for. Simply, I have a table full of alphanumeric results such as 'ADAPR2014' that needs to be converted to numeric. I could write code that says if a then 1, if b then 2, etc but I am assuming there is a function that will do similar and save me 26 lines of code. – Jayson Jun 13 '17 at 02:20
  • @Jayson Why on earth would A magically be equivalent to 1? From the last 8 words of my first comment "_what (do) you expect to happen for (non-numeric) values_"? More specifically (**but please be aware I don't have any desire to go though 1001 _specific_ examples**): `ADAPR2014` is quite clearly ***NOT numeric***! What should its numeric value be? No one can read your mind to guess what your requirements are. So it behoves you to state those requirements! (As an aside the string looks more like a partial date "April 2014" with a prefix than any kind of numeric _at all_!) – Disillusioned Jun 13 '17 at 03:29
  • @Jayson you should pay a bit attention to your string. e.g. is the length of the string always 9? Are the first two chars always 'AD'? Judging by the example, the middle chars 3 to 6 represent a month, so one approach might be to take the right 4 chars as year and append to the end the numeric equivalent of the month. In your example this would lead to "201404", which then can be converted to number. Anyways, 'AD' is still unclear. And as mentioned by craig: simply replacing the chars with numbers is kind of the worst idea I read here so far... – Tyron78 Jun 13 '17 at 09:01
  • The reason for the conversion is because the data I have in my tables right now is alphanumeric and I am moving the data to a new location that can only take numeric. That is why I have asked for a way to convert Alphanumeric to numeric. I have over 20,000 records that need to be changed. No, there is no fixed value and no, there is no pattern. This seems to be too difficult of a question. The question was a simple yes or no, does this type of function exist. That is all I was asking. I'll go ahead with my plan B and use the replace function. Thank you. – Jayson Jun 13 '17 at 12:28

0 Answers0