Feeds:
Posts
Comments

Posts Tagged ‘Extract’

--create function dbo.udf_ExtractNumbersToFloat (@String varchar(max))
--returns float
--begin
------ ==== Test =======
DECLARE @String VARCHAR(1000)

SET @String = 'I can count 1 and 2 and 3 even 4.5';

------ ==== Test =======
WHILE PATINDEX('%[^0-9]%.%', @String) & gt;0
	SET @String = REPLACE(@String, SUBSTRING(@String, PATINDEX('%[^0-9]%', @String), 1), '')

DECLARE @Float FLOAT

SET @Float = convert(FLOAT, @String)

PRINT @Float -- test
	--    return  @Float
	--end
Advertisements

Read Full Post »

CREATE FUNCTION dbo.udf_ExtractNumbers (@String VARCHAR(max))
RETURNS INT

BEGIN
 -------- ==== Test =======
 --DECLARE @String varchar(1000)
 --SET @String = 'I can count 1 and 2 and 3 but what is 4.5';
 -------- ==== Test =======
 WHILE PATINDEX('%[^0-9]%', @String) > 0
  SET @String = REPLACE(@String, SUBSTRING(@String, PATINDEX('%[^0-9]%', @String), 1), '')

 DECLARE @Integer INT

 SET @Integer = convert(INT, @String)

 -- PRINT @Integer -- test
 RETURN @Integer
END
 -- select * from udf_ExtractNumbers ('I can count 1 and 2 and 3 but what is 4.5')

Result:

Read Full Post »

-- SQL Server 2000/2005 (or 2008)
SELECT convert(CHAR(12), GETDATE(), 114) -- This is more widely used

SELECT DATEADD(Day, 0 - DATEDIFF(Day, 0, GetDate()), GetDate()) -- Retaining DateTime format

-- SQL Server 2008
SELECT CONVERT(TIME, GETDATE()) AS HourMinuteSecond
	,CONVERT(DATE, GETDATE(), 101) AS DateOnly

Read Full Post »

declare @passed varchar(max)

set @passed = 'FirstBit exec dbo.usp_Sites 4'


print substring(@passed, 1,  -- Change 1 to the vlaue you want the extraction to start from
		patindex('% exec %', @passed) - 1 -- Chage " exec " to any value in the string till which you want it extracted
	)

print substring(@passed, 
		patindex('% exec %', @passed) + 1, 
		len(@passed) - patindex('% exec %', @passed)
	)

-- The function Left() and Right() are also used when you want the extraction to start from either 
-- left most or right most side. 

Read Full Post »