SQL converting mixed numeric/alphanumeric string to number

opopanax666

New member
Joined
Jun 25, 2015
Messages
1
Reaction score
0
Points
0
Location
Belgium
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:
Code:
SELECT dbo_Transacties01.Jaar, dbo_Transacties01.Maand, dbo_Transacties01.CODEWINKEL, dbo_Transacties01.REFERTE, Count(dbo_Transacties01.REFERTE) AS AantalVanREFERTE
FROM dbo_Transacties01
WHERE Val([REFERTE])>0
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...

TIA,
James
 
Back
Top