Displaying everything to the left of a delimiter

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

SQL Server 2000 Forums

SQL Server Development (2000)

Displaying everything to the left of a delimiter

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-04 : 22:27:42

Travis Brown writes "I have a column that looks like
230
230-233
230-233-234
240
240-241
240-241-242
etc.

I need to return everything to the left of the - delimiter.

I (mostly) have sucessfully done this by finding the position of the - with PATINDEX and using it as an expression in LEFT (I learn a new thing every day!) then -1 to get rid of the first delim.

Some of the rows with no delimiter (i.e. 230, 240) will of course return PATINDEX values of 0, which I need to work around or else I won't get a returned value for rows with no delimiter. I tried replacing 0 with '', but I'm guessing SQL doesn't like it in LEFT statments. I temporarily used 50 as the REPLACE value just because I know there are no non-delimited values greater than 50 characters (actually, 4 characters, but I like being safe).

Is there a way to replace all PATINDEX values = 0 with NULL, and would that solve my problem?

SELECT DISTINCT
dbo.CCM_ContentRecord.rec_id, dbo.CCM_ContentData.dat_nav_title, dbo.CCM_ContentRecord.rec_parent_path,
LEFT(dbo.CCM_ContentRecord.rec_parent_path, REPLACE(PATINDEX('%-%', dbo.CCM_ContentRecord.rec_parent_path), '0', '50') - 1) AS pp
FROM dbo.CCM_ContentRecord INNER JOIN
dbo.CCM_ContentData ON dbo.CCM_ContentRecord.rec_id = dbo.CCM_ContentData.dat_rec_id

Clear as mud?"

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-03-05 : 04:46:40

The simple way around this problem is to stop it happening in the first place. If you append a hyphen then you can't get 0 as the result of the PATINDEX.

... PATINDEX('%-%', dbo.CCM_ContentRecord.rec_parent_path + '-') ...

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-03-05 : 08:01:21

... or use case to check when PATINDEX returns 0 :-

SELECT
rec_parent_path, 
case when PATINDEX('%-%', rec_parent_path)=0 then rec_parent_path
	else left(rec_parent_path,PATINDEX('%-%', rec_parent_path)-1) end,
FROM CCM_ContentRecord

============
The Dabbler!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources