SQL Substring and Last index of SQL Substring and Last index of sql sql

SQL Substring and Last index of

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