CREATE FUNCTION dbo.GetNumbers(@DATA VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
RETURN LEFT(
SUBSTRING(@DATA, PATINDEX(‘%[0-9.-]%’, @DATA), 8000),
PATINDEX(‘%[^0-9.-]%’, SUBSTRING(@DATA, PATINDEX(‘%[0-9.-]%’, @DATA), 8000) + ‘X’)-1)
END

Example:
INSERT INTO @Temp VALUES(‘2.1 miles’)
INSERT INTO @Temp VALUES(‘4 miles’)
INSERT INTO @Temp VALUES(‘Approximately 6.5 miles’)
INSERT INTO @Temp VALUES(‘3.9′)
INSERT INTO @Temp VALUES(‘7.2miles’)
INSERT INTO @Temp VALUES(”)
INSERT INTO @Temp VALUES(NULL)
INSERT INTO @Temp VALUES(‘No Numbers Here’)
INSERT INTO @Temp VALUES(‘approximately 2.5 miles, but less than 3′)

SELECT DATA, dbo.GetNumbers(DATA)
FROM @Temp

OUTPUT:
Data Numbers
—————————————- ——-
2.1 miles 2.1
4 miles 4
Approximately 6.5 miles 6.5
3.9 3.9
7.2miles 7.2

Introduction

Developers and database administrators have long debated methods for paging recordset results from Microsoft SQL Server, trying to balance ease of use with performance. The simplest methods were less efficient because they retrieved entire datasets from SQL Server before eliminating records which were not to be included, while the best-performing methods handled all paging on the server with more complex scripting. The ROW_NUMBER() function introduced in SQL Server 2005 provides an efficient way to limit results relatively easily.

Paging Efficiency

Select
CONVERT(nvarchar(4), ROW_NUMBER() OVER (ORDER BY [Auditing].[AuditQuestion].[Text])) + ‘. ‘ + [Auditing].[AuditQuestion].[Text] AS AuditQuestionText
…..

There are two main keywords used for deleting data from a table: TRUNCATE and DELETE. Although each achieves the same result, the methods employed for each vastly differ. There are advantages, limitations, and consequences of each that you should consider when deciding which method to use. Solution
Deleting Data Using TRUNCATE TABLE

Microsoft SQL Server (T-SQL)

In a select use:

select case when Obsolete = ‘N’ or InStock = ‘Y’ then ‘YES’ else ‘NO’ end
In a where clause, use:

where 1 = case when Obsolete = ‘N’ or InStock = ‘Y’ then 1 else 0 end