Solved

Remove all characters to the left of a specified character

Posted on 2011-03-08
2
338 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
ID: 35075992
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
ID: 35082889
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

896 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

14 Experts available now in Live!

Get 1:1 Help Now