vikash4all
Member
Hi All,
i have a CallLog table with CallID as varchar column, for a perticular region, we have CallID as all numeric values, but i found some records where i found alphanumeric values.
Is there any way in t-sql to separate these alphanumeric values to another table for auditing.
Thanks in advance.
Vikash
bretbhol
Member
Hi Vikash,
this is quite easy by error handling method in SSIS,
as you said that for a perticular region 'X', all the CallIDs will be numeric only.
In SSIS, use "derived column" transformation or "data conversion" transformation to type cast the input CallID to int or bigint, and redirect the error line to a table i.e. "CallIDError".
This way you will get all those CallIDs which consists of alphanumeric characters.
--Bhol
vikash4all
Member
Thanks Bhol,
i followed your method, and it is working perfectly.
But isn't there any way to do the same in t-sql, as per day i have go through lots of table where i have to face this type of problem.
If there will be anyway to do the same in SSMS, then that will be more helpful.
Anyways thanks a lot for the solution.
Hello Vikash,
use the below query to list all alphanumeric CallIDs from your CallLog table:
select * INTO CallLog_alphaNumeric from CallLog
where CallID like '%[^0-9]%' and CallID like '%[^a-z]%'Hope this will solve your problem
You must log in to post.
