[RESOLVED] Replacing Left of a character

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

Could any of this help?

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

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.

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.

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

sql Code:

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

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

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 ?

sql Code:

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

That it!!!!!

Thank you!!!!