Select everything after a character in SQL Server

Question

How can I extract the string after a specified character in SSMS?

For example "X1234" -> "1234". For this, the string will always begin with X.

What I have tried so far:

SUBSTRING(a.string,1,CHARINDEX('X',a.string))

which returns the 1st character after the X, though does not extend to all characters after X

Does this answer your question? [Is there a LastIndexOf in SQL Server?](https://stackoverflow.com/questions/39002025/is-there-a-lastindexof-in-sql-server/39002164) What *have* you tried to solve the problem? Why didn't it work? — Thom A, Mar 30 '21 at 10:20
Corrected with an example of what I tried, thanks for the link! — BaronG, Mar 30 '21 at 10:25

score 2 · Accepted Answer · answered Mar 30 '21 at 10:23

2

If all the string starts with X then you just can select substring starting from second character as below:

select substring('X1234',2,len('X1234'))

Or if there is chance to start with other characters then first you can use case when check whether the first character is X or not.

select case when left('X1234',1)='X' then substring('X1234',2,len('X1234'))end

answered Mar 30 '21 at 10:23

Kazi Mohammad Ali Nur Romel

15,301
2
13
26

1

Don't use charindex('X',string) since it will return location of x wherever X exists in that string not only for the first character. – Kazi Mohammad Ali Nur Romel Mar 30 '21 at 10:25

score 1 · Answer 2 · answered Mar 30 '21 at 10:32

If you want a dynamic search pattern you can use a combination of substring and charindex:

DECLARE @Input NVARCHAR(100) = 'X1234'
DECLARE @SearchString CHAR = 'X'

DECLARE @PosSearchString INT = CHARINDEX(@SearchString, @Input)

SELECT @Input AS InputString
      ,@PosSearchString AS PosSearchString
      ,CASE
         WHEN @PosSearchString > 0 THEN SUBSTRING(@Input, @PosSearchString+1, LEN(@Input) - @PosSearchString)
         ELSE ''
       END AS AfterSearchString

score 1 · Answer 3 · answered Mar 30 '21 at 10:34

1

You can try with PATINDEX for Patern match

select substring('X1234',patindex('%[a-z][0-9]%', 'X1234')+1, 100)

answered Mar 30 '21 at 10:34

B.Muthamizhselvi

642
4
13

score 1 · Answer 4 · answered Mar 30 '21 at 10:46

If you want the value from the second character onward, I would suggest stuff():

select stuff(str, 1, 1, '')

If you specifically want to remove all characters up to the first occurrence of a character, such as 'x', then:

select stuff(str, 1, charindex('x', str), '')

If you want to do this conditionally -- so the string is returned even when 'x' is not in the string:

select stuff(str, 1, charindex('x', str + 'x'), '')

If you wanted everything up to the first digit to be removed:

select stuff(str, 1, patindex('%[0-9]%', str + '0') - 1, '')

Select everything after a character in SQL Server

4 Answers4