Change the above query as per need.
Find a String in a String in SQL Server
Learn how you can find data in a string and extract it, no matter where it's located.
2016-03-10
36,662 reads
Change the above query as per need.
--Find only characters from string : DECLARE @Temp VARCHAR(100)='548STA123Ldfgh45df45df5446fg54645dfg546'; Declare @NumRange AS varchar(50) = '%[0-9]%'; While PatIndex(@NumRange, @Temp) > 0 SET @Temp = Stuff(@Temp, PatIndex(@NumRange, @Temp), 1 ,'') SELECT @Temp --==================================== Find only integers from string : DECLARE @var VARCHAR(100)='5STA123Ldfgh45df45df5446fg54645dfg546'; DECLARE @intdata INT SET @intdata = PATINDEX('%[^0-9]%', @var) BEGIN WHILE @intdata > 0 BEGIN SET @var = STUFF(@var, @intdata, 1, '' ) SET @intdata = PATINDEX('%[^0-9]%', @var ) END END Select ISNULL(@var,0) --Find a float value from string : DECLARE @var VARCHAR(100)='STA123.26dfg'; DECLARE @intdata INT SET @intdata = PATINDEX('%[^0-9]%', @var) BEGIN WHILE @intdata > 0 BEGIN SET @var = STUFF(@var, @intdata, 1, '' ) SET @intdata = PATINDEX('%[^0-9.]%', @var ) END END Select ISNULL(@var,0)