View Full Version : SQL converting mixed numeric/alphanumeric string to number

2015-06-25, 08:45 AM
Hello everyone,

I have a text-column where users should have inserted only postcodes, but have also entered the names of towns.

I usually use Access to quickly make a query, and then convert that solution to T-SQL to use with Excel/SQL connection string (usually simple SELECT queries).
But in this case I can't convert what works in Access to T-SQL, because the functions differ too much.

What I got working in Access is this:

SELECT dbo_Transacties01.Jaar, dbo_Transacties01.Maand, dbo_Transacties01.CODEWINKEL, dbo_Transacties01.REFERTE, Count(dbo_Transacties01.REFERTE) AS AantalVanREFERTE
FROM dbo_Transacties01
GROUP BY dbo_Transacties01.Jaar, dbo_Transacties01.Maand, dbo_Transacties01.CODEWINKEL, dbo_Transacties01.REFERTE
HAVING dbo_Transacties01.Jaar>=2013;

So, I need to retrieve only the records that contain only numericals, and then group them on year, month, CODEWINKEL, and the numerical value, and then count how many times they occur.

But I can't seem to find a way in T-SQL to make this work (tried "isnumerical", "cast", "convert", "like" and all combinations of these)...

Maybe someone here can help me?..

PS: I can't change anything to the source table...