Finding "strings" without extensions

Question

Post reply

Finding "strings" without extensions

kgbs12

Grasshopper

Points: 20
More actions
May 4, 2010 at 4:28 pm

#218542

Morning Folks.
I have a document table, thats has column called Name (nvarchar), This column includes the name and extension of a document for example document1.doc, document2.pdf etc
How do I find document names that do not have an extension?
I can find the extensions using either of the following queires
COALESCE(RIGHT(name, NULLIF(CHARINDEX('.', REVERSE(name)) - 1, -1)), name, '') AS Filename
or
Reverse(left(reverse(name), charindex('.',reverse(name)))) AS FileExtension
but no idea on how to produce the document names that do not have extensions.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Ummmm.... wouldn't you just find all the names that don't have a period in them? i.e. Use CHARINDEX or NOT LIKE?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 2

Something like this?

SELECT *

FROM

(

SELECT 'abc.de' col UNION ALL

SELECT 'abd'

) t

WHERE CHARINDEX('.',col) =0

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

kgbs12 Grasshopper Points: 20 More actions · Answer 3

Thanks Lutz

I used your where statement, and I've got the results I need.

LutzM SSC Guru Points: 107049 More actions · Answer 4

Glad I could help. 😀

But it was just the coded answer Jeff suggested first... (it's usually the other way around 😀 )

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

~Edit : My bad, posted in the wrong thread! 😀 ! Sorry!