CODEWITHSUNDEEP
sqlsql-serverstring
BaronG
BaronG

Reputation: 80

Select everything after a character in SQL Server

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

Upvotes: 1

Views: 7827

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1271161

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

Upvotes: 1

B.Muthamizhselvi
B.Muthamizhselvi

Reputation: 642

You can try with PATINDEX for Patern match

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

Upvotes: 1

Tyron78
Tyron78

Reputation: 4197

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

Upvotes: 1

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

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

Upvotes: 2

Related Questions