T-SQL Trim &Nbsp (And Other Non-Alphanumeric Characters)

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