Solved

How to get some text based on a char

Posted on 2014-01-13
5
291 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 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 59

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

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how the fundamental information of how to create a table.
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…

770 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