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