[RESOLVED] Replacing Left of a character

[Always_Confused]

Hi,

MSSQL 2000 question:

I need an SQL statement that would take e.g. "TAG/ 123456" and delete everything left of the forward slash giving me a result of "123456". I can not give a count as there are many values that may vary.

Select Replace(partname????????) from orders

Thanks

[BManke]

Could any of this help?

http://msdn2.microsoft.com/en-us/lib...1(SQL.80).aspx

[kfcSmitty]

MSSQL has a Right() function you could use.

It goes as follows: RIGHT ( character_expression , integer_expression )

So something like right(TAG/123456, 6) would work.

Again, depending on which language you're using, you could use a function to get the location of the /, and use the right function to grab anything before that character.

Or you could use a split function to split on "/" and grab the second index.

[Always_Confused]

Like I said, I can not set a count. Too many variables in the size of the string right of the forward slash. Same goes for the left side also.

[CVMichael]

Execute this code in the database you are using. It will create a function that will remove everything from the string except numbers.

CREATE FUNCTION [dbo].[fn_GetNumbers](@Str NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    -- SELECT dbo.fn_GetNumbers('testing.\.234...77..116/ghj (&(*&()((')
    
    DECLARE @RetVal NVARCHAR(MAX)
    DECLARE @I INT
    
    SET @RetVal = @Str
    SET @I = 1
    
    WHILE @I <= LEN(@RetVal) BEGIN
        IF CHARINDEX(SUBSTRING(@RetVal, @I, 1), '0123456789') = 0 BEGIN
            SET @RetVal = REPLACE(@RetVal, SUBSTRING(@RetVal, @I, 1), '')
            SET @I = @I - 1
        END
        
        SET @I = @I +1
    END
    
    RETURN @RetVal
END

[Always_Confused]

I need everything to the left of the forward slash and the slash it self. I will not rule out the the string may be TAG/ 123AB23 or TAG/ WE4051 or AB422/ 1234567890

[CVMichael]

You are contradicting yourself, in the first post you said you need the numbers, now you say you want everything to the left of slash....

But I think you mean everything AFTER the slash ? right ?
Something like this maybe ?

SELECT LTRIM(SUBSTRING('AB422/ 1234567890', CHARINDEX('/', 'AB422/ 1234567890') + 1, 1000))

[Always_Confused]

That it!!!!!

Thank you!!!!