CODEWITHSUNDEEP
sqlsql-serversql-server-2008
satyajit
satyajit

Reputation: 2700

Truncating Strings from a column name

I have a column which has values divided by colon ":". For example DT:CSDF , SFT:TAHS etc...

I just need to take the right side i.e. CSDF,TAHS etc

How do I do it in the select clause?

Upvotes: 1

Views: 98

Answers (4)

Simon Logic
Simon Logic

Reputation: 408

SELECT SUBSTRING(fieldname, CHARINDEX(':', fieldname) + 1, LEN(fieldname)) 
FROM ...

More t-sql string functions you can find here: http://msdn.microsoft.com/en-US/library/ms181984(v=sql.90).aspx

Upvotes: 0

Randy
Randy

Reputation: 16677

something like this:

SUBSTR( INSTR( mycol, ':' ) )

Upvotes: 0

RThomas
RThomas

Reputation: 10882

Like this:

SELECT SUBSTRING(YourField, 
                 CHARINDEX(':', YourField) + 1, 
                 LEN(YourField)
                 ) AS YourNewField

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 135011

If you will never have dots, you can use this

PARSENAME(REPLACE(ColumnName,':','.'),1)

example

DECLARE @v VARCHAR(100) = 'DT:CSDF'


SELECT PARSENAME(REPLACE(@v,':','.'),1)

otherwise use PATINDEX and RIGHT

SELECT RIGHT(ColumnName,LEN(ColumnName) -PATINDEX('%:%',ColumnName))

Example

DECLARE @v VARCHAR(100) = 'DT:CSDF'


SELECT RIGHT(@v,LEN(@v) -PATINDEX('%:%',@v))

Upvotes: 3

Related Questions