Member Avatar for divyakrishnan

Hi,
I have a table field with var char data type having values like TVM/345/2000,TVM/01/00,TSR/42/01 etc.I want to extract the sub string from first location to the second '/' location as TVM/345/.
Is there any built in function to solve is?

Thanks

  • 2 Contributors
  • 4 Replies
  • 116 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by divyakrishnan
Member Avatar for urtrivedi

SYNTAX

select substring(COLNAME, 1, CHARINDEX('/',COLNAME, CHARINDEX('/',COLNAME)+1)) FROM TABLENAME

EXAMPLE

select substring('TVM/345/2000,TVM/01/00,TSR/42/01', 1,CHARINDEX('/','TVM/345/2000,TVM/01/00,TSR/42/01',CHARINDEX('/','TVM/345/2000,TVM/01/00,TSR/42/01')+1))
Member Avatar for divyakrishnan

Thank You Very much..
There is one more thing I want to do.
Can I update the fields like TVM/01/00 as TVM/01/2000?

Member Avatar for urtrivedi

before running this query keep backup of that column. or do it in dummy database

update tablename set colname=replace(colname,'TVM/01/00','TVM/01/2000')
Edited by urtrivedi because: n/a
Member Avatar for divyakrishnan

It is not like that.The field is a dynamic field..I got the solution..Thanks for your support...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.