I have one column like this in SQL Server and I need get this codes
## Title## حساب-س1-2564492-305-151ساير سپرده ها حساب-س151.305.881307.1ساير سپرده ها حساب-س1-1547032-305-151ساير سپرده ها حساب-س1-96658-305-651ساير سپرده ها حساب-س1-7349772-305-751ساير سپرده ها حساب-س2-96658-305-851ساير سپرده ها حساب-س1-5545530-305-951ساير سپرده ها
How can I pull out the following data from this column?
This code has 1-2564492-305-151, 151.305.881307.1 titles
## Title## 1-2564492-305-151 151.305.881307.1 1-1547032-305-151 1-96658-305-651 1-7349772-305-751 2-96658-305-851 1-5545530-305-951
I use this code but sometimes it doesn’t work
ALTER FUNCTION [dbo].[FindNumbers_Slash]
(
@inputstring VARCHAR(100)
)
RETURNS VARCHAR(100)
AS
BEGIN
--declare variables---
DECLARE @count1 SMALLINT
DECLARE @len1 SMALLINT
DECLARE @word VARCHAR(100)
DECLARE @char1 CHAR
--Assignment---
SET @word = ''
SET @count1 = 1
SET @len1 = DATALENGTH(@inputstring)
WHILE @count1 <= @len1
BEGIN
SET @char1 = SUBSTRING(@inputstring ,@count1 ,1)
IF (@char1 BETWEEN '0' AND '9')
OR @char1 = '/'
BEGIN
SET @word = @word + SUBSTRING(@inputstring ,@count1 ,1)
END
ELSE
BEGIN
IF RIGHT(@word ,1) <> '-'
AND LEN(@word) <> 0
SET @word = @word + '-'
END
SET @count1 = @count1 + 1
END
RETURN @word
END
This code is 85% OK but not 100%.
Advertisement
Answer
I think it is because of the language substring() doesn’t work properly. When I reverse it and apply the substring() it worked.
IF OBJECT_ID('FindNumbers_Slash_V2') IS NOT NULL
DROP FUNCTION [dbo].[FindNumbers_Slash_V2];
GO
CREATE FUNCTION [dbo].[FindNumbers_Slash_V2]
(
@InputString NVARCHAR(1000)
)
RETURNS NVARCHAR(1000)
AS
BEGIN
DECLARE @Word NVARCHAR(1000)
SET @Word = SUBSTRING(@InputString
,PATINDEX('%[0-9]%',@InputString)
,LEN(@InputString)+1 - PATINDEX('%[0-9]%',@InputString)
)
SET @Word = REVERSE(@Word)
SET @Word = SUBSTRING(@Word
,PATINDEX('%[0-9]%',@Word)
,LEN(@Word)+1 - PATINDEX('%[0-9]%',@Word)
)
RETURN REVERSE(@Word)
END