best way remove extension

[peac3]

Hi guys,

I am trying to remove extension _EXT from the record if there is any and at the moment I used the below query.

SELECT @type = case when charindex('_EXT', type) > 0 then substring(type, 1, len(type)-len('_EXT')) else type end
	FROM table

But I found this query is abit too complex, do you guys have any simpler query for this purpose?

thanks guys,

 

[markros]

I think it's simple enough, but did you want to get the first record as a variable? Also, what if you have _ext in the middle of the record?

PluralSight Learning Library

 

[peac3]

Hi markros,

Yes, it's simple enough but I think there's still another way still better.

And yes, I need first rec as variable.

we don't have _ext in the middle of the record, so always at the end.

thanks,

 

[bborissov]

SELECT REPLACE(YourField,'_EXT','')

But I'm not sure if it is better

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[peac3]

hi bborissov

your query looks like longer than mine, here's the statistics

your query:
  Client processing time	1012.3
  Total execution time	1296.3
  Wait time on server replies	284

my query:
  Client processing time	1013.8
  Total execution time	1290
  Wait time on server replies	276.2

 

[markros]

If you only need the first record, then change your query to

select top (1) @type = case when charindex('_EXT', type) > 0 then substring(type, 1, len(type)-len('_EXT')) else type end
FROM table ORDER BY SomeColumn

then it would not make difference which code you'll use to strip the extension.

PluralSight Learning Library