ALTER FUNCTION [dbo].[ExtractInteger](@NumStr NVARCHAR(2000))

RETURNS VARCHAR(1000)

AS

BEGIN

WHILE PATINDEX('%[^0-9]%',@NumStr)> 0

SET @NumStr = REPLACE(@NumStr,SUBSTRING(@NumStr,PATINDEX('%[^0-9]%',@NumStr),1),'')

return @NumStr

END





select

Item_SR

, LEFT(Item_SR,Charindex('/',Item_SR)-1) as Serial_1

, dbo.ExtractInteger(Substring(Item_SR,Charindex('/',Item_SR),10)) as Serial_2

from Wo_Civil_Dtl

order by

convert(int,LEFT(Item_SR,Charindex('/',Item_SR)-1))

, convert(int,(dbo.ExtractInteger(Substring(Item_SR,Charindex(N'/',Item_SR)+1,10))))

تصحيج الموضوع :
كود لترتيب الحروف والارقام بستخدام مايكروسوفت sql ، اريد كتابته الى اوراكل sql
ALTER FUNCTION [dbo].[ExtractInteger](@NumStr NVARCHAR(2000))

RETURNS VARCHAR(1000)

AS

BEGIN

WHILE PATINDEX('%[^0-9]%',@NumStr)> 0

SET @NumStr = REPLACE(@NumStr,SUBSTRING(@NumStr,PATINDEX('%[^0-9]%',@NumStr),1),'')

return @NumStr

END





select

Item_SR

, LEFT(Item_SR,Charindex('/',Item_SR)-1) as Serial_1

, dbo.ExtractInteger(Substring(Item_SR,Charindex('/',Item_SR),10)) as Serial_2

from Wo_Civil_Dtl

order by

convert(int,LEFT(Item_SR,Charindex('/',Item_SR)-1))

, convert(int,(dbo.ExtractInteger(Substring(Item_SR,Charindex(N'/',Item_SR)+1,10))))