Select query to remove non-numeric characters

by

See this blog post on extracting numbers from strings in SQL Server. Below is a sample using a string in your example:

DECLARE @textval NVARCHAR(30)
SET @textval="AB ABCDE # 123"

SELECT LEFT(SUBSTRING(@textval, PATINDEX('%[0-9.-]%', @textval), 8000),
           PATINDEX('%[^0-9.-]%', SUBSTRING(@textval, PATINDEX('%[0-9.-]%', @textval), 8000) + 'X') -1)

More Related Contents:

  1. Levenshtein distance in T-SQL
  2. How to get sp_executesql result into a variable?
  3. How can I use optional parameters in a T-SQL stored procedure?
  4. T-SQL Decimal Division Accuracy
  5. Selecting a Record With MAX Value
  6. T-SQL Pivot? Possibility of creating table columns from row values
  7. WHERE Clause vs ON when using JOIN
  8. What is a “batch”, and why is GO used?
  9. TSQL divide by zero encountered despite no columns containing 0
  10. Partition Function COUNT() OVER possible using DISTINCT
  11. T-SQL loop over query results
  12. How to insert default values in SQL table?
  13. How to Round a Time in T-SQL
  14. How to convert a “dd/mm/yyyy” string to datetime in SQL Server?
  15. Return a value if no rows are found in Microsoft tSQL
  16. How to pivot table with T-SQL?
  17. Using Aliases in Where Clause or an Alternative Option?
  18. Testing for inequality in T-SQL
  19. Can I use the MERGE statement in SQL Server 2005?
  20. Count Returning blank instead of 0
  21. Replace a newline in TSQL
  22. Search for one value in any column of any table inside a database
  23. How to use an Alias in a Calculation for Another Field
  24. Check if a varchar is a number (T-SQL)
  25. Using a cursor with dynamic SQL in a stored procedure
  26. Using IF ELSE statement based on Count to execute different Insert statements
  27. Check if table exists and if it doesn’t exist, create it in SQL Server 2008
  28. String.Format like functionality in T-SQL?
  29. How to parse string into date?
  30. Stored procedure hangs seemingly without explanation

Leave a Comment