Finding a substring - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Finding a substring

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-02-04 : 07:16:49

I have a varchar field. I need to locate all records for which the last three characters in the field are 'pdf' but the character before 'pdf' is NOT a '.'

So, if I had

thisisastoredfile.pdf
thisisnotaviewablefilepdf

the query would return the record with

thisisnotaviewablefilepdf

How can I do this please?

(I am using MS SQL Server 2005)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-04 : 07:36:05

replace(columnname,'.','')

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-02-04 : 07:49:33

quote:
Originally posted by bklr

replace(columnname,'.','')

Thanks for your reply, but how will this return the required records(s)?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-04 : 07:55:46

declare @tab table(name varchar(32))

insert into @tab select 'thisisastoredfile.pdf' union all select
'thisisnotaviewablefilepdf'

select top 1 replace(name,'.','') as val from @tab order by val desc

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-04 : 07:55:51

select
case when column like '%.%' then replace(columnname,'.','') else columnname end
from urtable

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-02-04 : 08:01:39

try this


DECLARE @foo TABLE (
	[col] VARCHAR(10)
	)
INSERT @foo
      SELECT 'foo.pdf'
UNION SELECT 'barpdf'
UNION SELECT 'tr'

SELECT
    *
FROM
    @foo
WHERE
    LEN([col]) > 3
    AND RIGHT([col], 4) LIKE '[^.]pdf'

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 08:42:46

[code]
SELECT * FROM Table WHERE Col NOT LIKE '%.pdf' AND Col LIKE '%pdf'
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-04 : 08:48:14

[code]SELECT * FROM @Foo WHERE Col LIKE '%[^.]pdf'[/code]

E 12°55'05.63"
N 56°04'39.26"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-02-04 : 08:49:33

Thinking some more -- you don't need the check for length. This should be quick as it only needs 1 string op


DECLARE @foo TABLE (
	[col] VARCHAR(10)
	)
INSERT @foo
      SELECT 'foo.pdf'
UNION SELECT 'barpdf'
UNION SELECT 'tr'

SELECT
    *
FROM
    @foo
WHERE
    [col] LIKE '%[^.]pdf'

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 - 2009-02-04 : 08:50:39

quote:
Originally posted by Peso

SELECT * FROM @Foo WHERE Col LIKE '%[^.]pdf'

E 12°55'05.63"
N 56°04'39.26"

LOL Peso

I did notice my over complication. but about 10 seconds after you did!

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-04 : 08:52:13

Well, your suggestion handles this sample data too as my suggestion does

UNION SELECT 'pdf'

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources