T-SQL trim   (and other non-alphanumeric characters)
This page has a sample of how you can remove non-alphanumeric chars:
-- Put something like this into a user function:
DECLARE @cString VARCHAR(32)
DECLARE @nPos INTEGER
SELECT @cString = '90$%45623 *6%}~:@'
SELECT @nPos = PATINDEX('%[^0-9]%', @cString)
WHILE @nPos > 0
BEGIN
SELECT @cString = STUFF(@cString, @nPos, 1, '')
SELECT @nPos = PATINDEX('%[^0-9]%', @cString)
END
SELECT @cString
SQL Server : how to remove leading/trailing non-alphanumeric characters from string?
Well, if you know they are only at the beginning and end, you can do:
with t as (
select *
from (values ('www.google.com'), ('''www.google.com'''), ('/www.google.com')) v(text)
)
select t.text, v2.text2
from t cross apply
(values (stuff(t.text, 1, patindex('%[a-zA-Z0-9]%', t.text) - 1, ''))
) v(text1) cross apply
(values (case when v.text1 like '%[^a-zA-Z0-9]'
then stuff(v.text1, len(text) + 1 - patindex('%[a-zA-Z0-9]%', reverse(v.text1)), len(v.text1), '')
else v.text1
end)
) v2(text2);
Here is a db<>fiddle.
Fastest way to remove non-numeric characters from a VARCHAR in SQL Server
I may misunderstand, but you've got two sets of data to remove the strings from one for current data in the database and then a new set whenever you import.
For updating the existing records, I would just use SQL, that only has to happen once.
However, SQL isn't optimized for this sort of operation, since you said you are writing an import utility, I would do those updates in the context of the import utility itself, not in SQL. This would be much better performance wise. What are you writing the utility in?
Also, I may be completely misunderstanding the process, so I apologize if off-base.
Edit:
For the initial update, if you are using SQL Server 2005, you could try a CLR function. Here's a quick one using regex. Not sure how the performance would compare, I've never used this myself except for a quick test right now.
using System;
using System.Data;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString StripNonNumeric(SqlString input)
{
Regex regEx = new Regex(@"\D");
return regEx.Replace(input.Value, "");
}
};
After this is deployed, to update you could just use:
UPDATE table SET phoneNumber = dbo.StripNonNumeric(phoneNumber)
Regex in LIKE Clause that accepts only Alphanumeric and dashes
You are testing for "whether any alphabet, number or dash is present in the string"
You should instead test whether any character other than alphabet, number or dash is present.
SET @bitInputVal = CASE WHEN @InputText LIKE '%[^A-Za-z0-9-]%' THEN 0 ELSE 1 END
SQL strip text and convert to integer
This should do the trick:
SELECT SUBSTRING(column, PATINDEX('%[0-9]%', column), 999)
FROM table
Based on your sample data, this that there is only one occurence of an integer in the string and that it is at the end.
Sql Select when string might contains spaces
Replace() should work for you.
WHERE REPLACE(telephone,' ','') = 01234567890
Related Topics
Does Union All Guarantee the Order of the Result Set
How to Write a Constraint Concerning a Max Number of Rows in Postgresql
Sqlite Equivalent of Row_Number() Over (Partition by ...)
Oracle Insert If Row Does Not Exist
Select SQL Server Database Size
How to List All Tables in a Schema in Oracle SQL
How to Select Every Row Where Column Value Is Not Distinct
Postgres Constraint for Unique Datetime Range
Merge Row Values into a CSV (A.K.A Group_Concat for SQL Server)
Sql: How to Get the Value of an Attribute in Xml Datatype
SQL Unique Constraint on a 2 Columns Combination
Get the Character Between First 2 Special Character in SQL
Unpivot with Dynamic Columns Plus Column Names
Oracle 11G - Check Constraint with Regex
Sql: Order by Using a Substring Within a Specific Column... Possible
How to Map Input and Output Columns Dynamically in Ssis