Solved

How to get some text based on a char

Posted on 2014-01-13
5
294 Views
Last Modified: 2014-01-13
Hi there

Hope you can help me?

I have a string like this

Fred Blog (Feb 2009)

Can someone please write a function that will get me ALL the text BEFORE the '('

i.e.

Fred Blog

thanks
0
Comment
Question by:mousemat24
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 11

Assisted Solution

by:Simone B
Simone B earned 100 total points
ID: 39777086
Try this:

SELECT LEFT(ColumnName,CHARINDEX('(', ColumnName)-1)

Open in new window

0
 

Author Comment

by:mousemat24
ID: 39777089
can you please help me so that I can use it as a function?

thanks
0
 
LVL 12

Accepted Solution

by:
Habib Pourfard earned 400 total points
ID: 39777094
Here is the function:
CREATE FUNCTION GetText(@Text VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
	RETURN SUBSTRING(@Text, 1, CHARINDEX('(', @Text) - 1)
END

Open in new window


SELECT dbo.GetText('Fred Blog (Feb 2009)')

Open in new window

0
 
LVL 14

Expert Comment

by:Michael Dyer
ID: 39777098
Here you go.

Assume you have a table named "MyTable"

Create one column named "INUPT" for your input string and one column named "NAME" for the trimmed name.

Then, run this procedure on the table:

     UPDATE MyTable SET NAME = LEFT(INPUT,LEN(INPUT)-CHARINDEX('(',INPUT))

This should put the trimmed name in the NAME column.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39777105
You have answers above, but here are a few notes:

Your substring will end up with an extraneous space; therefore, you may need to RTRIM the text if there is a space before '(' as in your example.
If the text you pass the function does not contain a '(', you will get an error; therefore, you may want to add an IF/CASE statement OR do this:
SUBSTRING(@Text, 1, CHARINDEX('(', @Text+'(') - 1)

Open in new window

0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

696 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