CODEWITHSUNDEEP
sqlsql-servert-sql
tp1
tp1

Reputation: 33

Pull only text left of "-"

I am using T-SQL. I have these two strings. 

AX20-xxxxxxxx
T11-xxxxxxxxx

For the first one I want to be able to pull AX

For the second I would like only T.

Upvotes: 3

Views: 98

Answers (3)

valex
valex

Reputation: 24144

In SQL Server you can use PATINDEX() to get first digit occurrence and then use LEFT() function to get left substring 

WITH T AS 
(
  SELECT 'AX20-xxxxxxxx' as Str
  UNION ALL
  SELECT 'T11-xxxxxxxxx' as Str
)
SELECT LEFT(Str,PATINDEX('%[0-9]%',Str)-1) FROM T

Upvotes: 3

John Cappelletti
John Cappelletti

Reputation: 81950

Take a peek at PatIndex()

Declare @S varchar(max) = 'AX20-xxxxxxxx'
Select Left(@S,PatIndex('%[0-9]%',@S)-1)

Returns

AX

Upvotes: 4

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Assuming you need to get the substring from the beginning to the occurrence of first number, use PATINDEX to get the index of the first number and SUBSTRING thereafter.

select substring(colname, 1, patindex('%[0-9]%',colname)-1)
from tablename

Upvotes: 3

Related Questions