Greetings all!
Does anyone know an effective and EFFICIENT method of replacing a range of characters using TSQL? The issue I am trying to resolve has to do with loading data files into SQL with field values that sometimes contain control characters (carriage return, line feed, tab, etc). Ideally what I would like to do replace any ASCII character (1-31) in a field value with a space. I'm certain that I could accomplish this by looping through the character numbers and doing a replace on each one individually but I am often dealing with millions of records and therefore am in need of a more efficient method that would allow me to do the replace in one pass.
This doesn't work but I'm thinking of something like: SELECT REPLACE([Field1],ASCII(1-31),' ')
Any thoughts on this will be greatly appreciated.
Many thanks!
Nate
Does anyone know an effective and EFFICIENT method of replacing a range of characters using TSQL? The issue I am trying to resolve has to do with loading data files into SQL with field values that sometimes contain control characters (carriage return, line feed, tab, etc). Ideally what I would like to do replace any ASCII character (1-31) in a field value with a space. I'm certain that I could accomplish this by looping through the character numbers and doing a replace on each one individually but I am often dealing with millions of records and therefore am in need of a more efficient method that would allow me to do the replace in one pass.
This doesn't work but I'm thinking of something like: SELECT REPLACE([Field1],ASCII(1-31),' ')
Any thoughts on this will be greatly appreciated.
Many thanks!
Nate