Hi @sourav dutta ,
Please check:
CREATE TABLE #test(id int, [name] char(55))
INSERT INTO #test VALUES(1,'Import-Asuri-598')
,(2,'Import-HPE-1045-Upload'),(3,'Import-Alti-1023(Pend)')
,(4,'Com-Asuri-948(1:30PM)'),(5,'Import-connect pid 1063')
,(6,'Import-JumpStart-PID-1051(irhythm)'),(7,'Import-Com-1071/1072')
CREATE FUNCTION [dbo].[GET_NUMBER] (@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^0-9\\.\\^0-9]%', @S) > 0
BEGIN
SET @S = STUFF(@S, PATINDEX('%[^0-9\\.\\^0-9]%', @S), 1, '');
END;
RETURN @S;
END
;WITH cte
as(SELECT *,
CASE WHEN CHARINDEX('(',[name])>0 THEN
SUBSTRING([name],CHARINDEX('(',[name]),LEN([name])-CHARINDEX('(',[name])+1)
WHEN PATINDEX('%[0-9]/[0-9]%',[name])>0 THEN
SUBSTRING([name],PATINDEX('%[0-9]/[0-9]%',[name])+2,LEN([name])-PATINDEX('%[0-9]/[0-9]%',[name])+1)
END name2
FROM #test)
,cte2 as(SELECT *,[dbo].[GET_NUMBER]([name]) n1,
[dbo].[GET_NUMBER](name2) n2
FROM cte)
SELECT id,[name],CASE WHEN n2 IS NOT NULL
THEN REPLACE(n1,n2,'')
ELSE [n1] END [Output] FROM cte2
UNION ALL
SELECT id,[name],n2
FROM cte2
WHERE PATINDEX('%[0-9]/[0-9]%',[name])>0
DROP FUNCTION [dbo].[GET_NUMBER]
Output:
If you have any question, please feel free to let me know.
Regards
Echo
If the answer is helpful, please click "Accept Answer" and upvote it.