SQL Substring and Last index of

i've got stuck with substring.

On input i've got a string that looks like Sometext (123456). Those digits at the end are ran开发者_JAVA技巧dom. I need to get only text from that string.

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

If we want to remove the sentence and keep the number we might do like this.

DECLARE @Text VARCHAR(MAX); 
SET @Text = 'Sometext (123456)'

SELECT SUBSTRING(@Text, CHARINDEX(' ', @Text) + 1, LEN(@Text)) As TextOutput

The result will be: (123456)

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))

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))