SQL Substring and Last index of

Question

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

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