Solved

Remove all characters to the left of a specified character

Posted on 2011-03-08
2
335 Views
Last Modified: 2012-05-11
I have a column of data that has text like "New Price, now only $5.99"  

I would like to remove all the characters to the left of the dollar sign.

It would leave only the $5.99
0
Comment
Question by:theideabulb
2 Comments
 
LVL 16

Expert Comment

by:sjklein42
Comment Utility
Something like this should do it:

SUBSTRING ( mystring , CHARINDEX ( mystring, '$' ), 10 )

Open in new window

see:

CHARINDEX ( expression1 ,expression2 [ , start_location ] )

http://msdn.microsoft.com/en-us/library/ms186323.aspx

SUBSTRING ( expression ,start , length )

http://msdn.microsoft.com/en-us/library/ms187748(v=sql.90).aspx
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
sjklein: the question is for MySQL, not mssql server.

the logic is the same, though, using the INSTR or LOCATE functions
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_instr
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_locate

and the substr / mid function(s):
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_mid
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring

watch out though about the order of the arguments in INSTR vs LOCATE (vs CHARINDEX), it's not always the same !
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now