Dear All

Thanks for your attention.

In an order history table 'orders', there is an varchar column 'order_number'.

e.g. 'OU123456', 'OU123456R1', 'OU987987987R11'

There is a 'R' when the order is revised. (Note that I know this table design is bad but it is an existing table which I could not replace)

I would like to get the revision number using sql,

e.g. 'OU123456',  0

'OU123456R1', 1

'OU987987987R11', 11

Note that the length of order number and revision varies.

The first two chars may contains 'R' e.g. 'RQ456456R1'

could anyone suggest some ideas? Thanks.

Best Regards

swivan

• Edited by swivan Monday, December 29, 2014 3:54 AM more information

Hi, if i am not wrong you can split with comma as you have stored with ",". Create one function in SQL. as below ...

               

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

--------------------------------------- CALLING PART

DECLARE @val AS VARCHAR(MAX)
SET @val = (SELECT [COLUMN NAME] FROM [TABLE NAME])
SELECT * FROM dbo.fnSplitString(@val,',')

--------------------------------------- OUT PUT

-------------------------------------- REST OF THE PART

NOW YOU CAN USE SQL BUILT IN FUNCTION "SUBSTRING"

As per your posted sample data this would work

SELECT STUFF(Order_Number,1,PATINDEX('%[0-9]%',Order_Number + '0')-1,'') AS RevisionNumber
FROM Orders
WHERE Order_Number LIKE '%R%'

Dear Sandeep

thanks and sorry for the misleading

order_number only contains

'OU123456'

'OU123456R1'

'OU987987987R11'

the comma is just to let reader know that no 'R'  means revision number equal to 0

I would like to get the smartest way to build the query, thanks.

Dear Visakh16

Thanks but the result seems not as wanted

result below

RevisionNumber

309136R1
310238R1

...

and it filter out orde_number that have 0 revision which is needed in my query

Thanks.

try below

select 'OU123456' Order_Number into #Orders union all
select 'OU123456R1' union all
select 'OU987987987R11' 

Select Order_Number,
case when CHARINDEX('R',Order_Number) > 0 then SUBSTRING(Order_Number,CHARINDEX('R',Order_Number)+1,LEN(Order_Number)) else '0' end 
from #Orders

Thanks

Saravana Kumar C

Dear Visakh16

Thanks but the result seems not as wanted

result below

RevisionNumber

309136R1
310238R1

...

and it filter out orde_number that have 0 revision which is needed in my query

Thanks.

Ok So you meant this?

SELECT CASE WHEN STUFF(Order_Number,1,PATINDEX('%R%',Order_Number),'') LIKE '%[A-Za-z]%' THEN 0
ELSE
COALESCE(NULLIF(STUFF(Order_Number,1,PATINDEX('%R%',Order_Number),''),''),0)
END  AS RevisionNumber
FROM Orders
WHERE Order_Number LIKE '%R%'

Select Order_Number
,ISNULL(nullif(REVERSE(SUBSTRING(REVERSE(Order_Number),0,CHARINDEX('R',REVERSE(Order_Number)))),''),0)

• Proposed as answer by Latheesh NKMVP Monday, December 29, 2014 5:19 AM
• Marked as answer by swivan 9 hours 40 minutes ago

Try the below:

select 'OU123456' Order_Number into #Orders union all
select 'RQ123456R1' union all
select 'OU987987987R11' 

Select 
	Order_Number,IsNull(nullif(SUBSTRING(REVERSE(Order_Number),0,CHARINDEX('R',REVERSE(Order_Number))),''),0)
FROM #Orders

Drop table #Orders

• Marked as answer by swivan 9 hours 40 minutes ago

Can I suggest to create two more columns for table 'order': Revised bit (default 0) and RevisedNumber and each time you update table data you can update these columns.

----------------------------------------------

Hope above help !

Doanh

Dear Doanh

Yes, but I need to migrate the old data to the new column also need the query.

Thanks.

You can use INSERT INTO ... SELECT .... FROM .... and/or UNION ALL SELECT ... FROM ...

-----------------------------------------------

Hope above help !

Doanh