?
Solved

How to get some text based on a char

Posted on 2014-01-13
5
Medium Priority
?
309 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
5 Comments
 
LVL 11

Assisted Solution

by:Simone B
Simone B earned 400 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 1600 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 61

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

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

594 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