exclude content - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

exclude content

Author

Topic

TommSaha
Starting Member

8 Posts

Posted - 2008-11-27 : 07:11:35

Hi all.

I am setting up a id badge printer, getting data from a sql database: Name, DoB, Job title

however on some job titles they have, for example: "Finance Officer (entry)"

(entry) is something to do with payroll/salary.

I have created a view to get the data as the badge software needs to get data from a View.

Is there a way I can have the view to exclude anything inside a bracket in the job title field?

Maybe truncate or something like that? It needs to be directly done in the sql.

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-27 : 07:42:20

If you know there's only going to be one (x) entry in each text field you can do this:


DECLARE @foo TABLE (
	[strings] VARCHAR(255)
	)

INSERT @foo
      SELECT 'abc (123) def'
UNION SELECT 'foo bar;'
UNION SELECT ''
UNION SELECT 'foo (123'
UNION SELECT '(fooooo'
UNION SELECT 'foo();Bar()'

SELECT
	LTRIM(RTRIM(LEFT([strings], CHARINDEX('(', [strings])- 1) + RIGHT([strings],LEN([strings]) - CHARINDEX(')', [strings]))))
FROM @foo
WHERE
	[strings] LIKE '%(%)%'
UNION SELECT
	LTRIM(RTRIM([strings]))
FROM
	@foo
WHERE
	[strings] NOT LIKE '%(%)%'

If there could be more then a recursive function might be a better plan.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

TommSaha
Starting Member

8 Posts

Posted - 2008-11-27 : 08:05:22

whoa, that sql is a little over my head!

only some people have a (something) in their job title. and the word changes from role to role, it is very annoying.

I've seen something like this on other forums:

SELECT     LEFT(Post_Description, CHARINDEX('(', Post_Description) - 1) AS Post_Description
FROM         dbo.emp_post_hist

But to be honest I don't fully understand it and I get a "invalid length parameter passed to substring" error. Something to do with Null values?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-27 : 08:47:06

What's happening with the example you posted is that it won't work if there isn't a '(' in the string.

My post might look a little daunting but it's not:

it

1) Builds a data set with some examples

2) Selects them all in the following way

2.1) For any string that has BOTH ( and ) in it it will perform a set of string functions to remove everything between ( and )

2.2) For any other string (NOT LIKE 2.1) it returns the string without modifications.

-- You can copy and paste the whole thing into a query analyser window and run it to see what it is doing.

For strings that might have more that 1 set of (asfaf) in them it won't work.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-27 : 08:49:38

If you don't care about any characaters at all after a '(' you can modify the sql you posted as:


SELECT     LEFT(Post_Description, CHARINDEX('(', Post_Description) - 1) AS Post_Description
FROM         dbo.emp_post_hist
WHERE Post_Description LIKE '%(%'
UNION ALL
SELECT     LEFT(Post_Description, CHARINDEX('(', Post_Description) - 1) AS Post_Description
FROM         dbo.emp_post_hist
WHERE Post_Description NOT LIKE '%(%'

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-27 : 08:55:38

You could use this , similar to what you have :


SELECT    case when charindex('(',Post_Description)=0 then Post_Description else LEFT(Post_Description, CHARINDEX('(', Post_Description) - 1) end AS Post_Description
FROM         dbo.emp_post_hist

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-27 : 09:22:55

The CASE approach may well be more or less efficient (I'm not sure).

With that CASE statement every value goes through a CHARINDEX operation first then gets processed.

Of course with the union select everything goes through a LIKE operation and then gets processed.

Neither would be able to adequately use an index I feel.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

TommSaha
Starting Member

8 Posts

Posted - 2008-11-27 : 09:30:51

THat last one works!

Also got this off a friend:
select LEFT(Post_Description + '(' ,CHARINDEX( '(' ,Post_Description + '(' )-1) as Post_Description from emp_post_hist

Appends brackets to everything and then removes them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources