Remove 'ID' from SQL query

Question

I have a query which adds or removes zeros from ID.

SELECT 
    LEFT(STUFF(SystemID +
               REPLICATE('0', 9), 7, 0, REPLICATE('0', 9 - LEN(SystemID))), 9) as OBJECT 
FROM
    Products

So results will be something like this:

ID1230034

How can I remove 'ID' using this query? So it should be:

Thanks for all help:)

score 2 · Accepted Answer · answered Feb 20 '17 at 14:00

2

You can use stuff to replace the first 2 chars of the string to an empty string:

SELECT STUFF(LEFT(STUFF(SystemID +
REPLICATE('0',9),7,0,REPLICATE('0',9-LEN(SystemID))),9), 1, 2, '') as OBJECT 
FROM Products

answered Feb 20 '17 at 14:00

Zohar Peled

76,346
9
62
111

score 1 · Answer 2 · answered Feb 20 '17 at 14:01

1

Use replace function .

SELECT REPLACE(LEFT(STUFF(SystemID +
REPLICATE('0',9),7,0,REPLICATE('0',9-LEN(SystemID))),9),'ID','') as OBJECT from Products

answered Feb 20 '17 at 14:01

Ronak Patel

570
3
14

score 0 · Answer 3 · answered Feb 20 '17 at 14:00

Use the function Right() -> https://msdn.microsoft.com/en-us/library/ms177532.aspx

assuming your ID's are always the same number of digits, do Right(ID,7)

Alternative Solution:

Use the Substring function -> http://www.w3resource.com/mysql/string-functions/mysql-substring-function.php

This can be helpful if the number of digits change :)

score 0 · Answer 4 · edited May 23 '17 at 11:53

A more generic solution that can cope with prefix/suffix non-numeric characters can be found here:

CREATE FUNCTION dbo.udf_GetNumeric
(
     @strAlphaNumeric VARCHAR(256)
)
RETURNS VARCHAR(256)
AS
BEGIN
    DECLARE @intAlpha INT
    SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)

    WHILE @intAlpha > 0
    BEGIN
        SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
        SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
    END

    RETURN ISNULL(@strAlphaNumeric,0)
END
GO

Usage:

SELECT dbo.udf_GetNumeric(SystemID) 
from from Products

Remove 'ID' from SQL query

4 Answers4