SQL Substring and Last index of

Question

Hi, i've got stuck with substring.

On input i've got a string that looks like Sometext (123456). Those digits at the end are random. I need to get only text from that string.

Answer 1

How about this?

DECLARE @Data TABLE (Val VARCHAR(20))
INSERT @Data VALUES ('Sometext (123456)')
INSERT @Data VALUES ('')
INSERT @Data VALUES (NULL)
INSERT @Data VALUES ('S(123456)')
INSERT @Data VALUES ('(123456)')

SELECT 
    CASE 
        WHEN CHARINDEX('(', Val) > 0 THEN 
            RTRIM(SUBSTRING(val,1, CHARINDEX('(', Val) - 1))
        ELSE Val
    END
FROM @Data

Answer 2

If you just want the first part up to the '(' you could try

declare @t varchar(50)
set @t = 'function (12343)'

select rtrim(substring(@t,1, charindex('(', @t)-1))

Answer 3

It really does depend on the format of your input string, but here is a slightly different approach using PATINDEX that will return the string until it matches a non A-Z character:

declare @text varchar(500); set @text = 'Sometext (123456)'
select SUBSTRING(@text, 0, PATINDEX('%[^A-Z]%' , @text))