returning a part of a string - 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 2000 Forums

SQL Server Development (2000)

returning a part of a string

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2004-03-16 : 16:26:40

I have these filenames and I'd like to return the chars that are before the dot.

Admin.zip
Managers.zip
West.zip

How do I do this using string functions in SQL Server?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-16 : 16:29:36

REVERSE is used in case a filename ever contains multiple dots.



SET NOCOUNT ON

CREATE TABLE Table1
(
Filename VARCHAR(500) NOT NULL
)

INSERT INTO Table1 VALUES('Admin.zip')
INSERT INTO Table1 VALUES('Managers.zip')
INSERT INTO Table1 VALUES('West.zip')

SELECT SUBSTRING(Filename, 1, LEN(Filename) - CHARINDEX('.', REVERSE(Filename)))
FROM Table1

DROP TABLE Table1

Tara

PeterG
Posting Yak Master

156 Posts

Posted - 2004-03-16 : 17:32:38

Thanks Tara.

But what if this is the script:

SET NOCOUNT ON

CREATE TABLE Table1
(
Filename VARCHAR(500) NOT NULL
)

INSERT INTO Table1 VALUES('C:\files\Admin.zip')
INSERT INTO Table1 VALUES('C:\files\Admin.zip')
INSERT INTO Table1 VALUES('C:\files\Admin.zip')

SELECT SUBSTRING(Filename, 10, LEN(Filename) - CHARINDEX('.', REVERSE(Filename)))
FROM Table1

DROP TABLE Table1

When I run this, I still get the .zip at th end. I don't exactly know how the CHARINDEX part of the code works.

PeterG
Posting Yak Master

156 Posts

Posted - 2004-03-16 : 17:45:12

Ok, I got it. The second parameter in the SUBSTRING function is length (# of chars from the starting point)!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-16 : 17:54:30

So do you have a solution when your data has the path information in the column? If not, let me know and I'll come up with that part.

Tara

PeterG
Posting Yak Master

156 Posts

Posted - 2004-03-16 : 18:54:04

Yea, I got the solution. Thanks again.