String Manipulation - 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)

String Manipulation

Author

Topic

jgallen23
Starting Member

17 Posts

Posted - 2007-01-12 : 13:52:11

I have a imageurl that looks something like:
6fe7651b-2ee3-4aea-a0c4-a7a2689edea9/blah.jpg

How would I create a function to insert _Small before the extension so it would look like:

6fe7651b-2ee3-4aea-a0c4-a7a2689edea9/blah_Small.jpg

Any assistance would be appreciated

jgallen23
Starting Member

17 Posts

Posted - 2007-01-12 : 14:05:37

I actually figure it out using a function. Let me know if there is a better way to do it

create function GetSmallImage(@img varchar(200),@size varchar(50))

returns varchar(200)

as
begin
declare @img2 varchar(150)

declare @ext varchar(4);
declare @a int
declare @b int

set @ext = RIGHT(@img,4)
set @a = charindex(@ext,@img)

set @img2 = substring(@img,0,@a)

return @img2+@size+@ext

end

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-12 : 14:06:01

replace(yourstring, '.jpg','_Small.jpg')

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-12 : 14:12:54

You are bad Jeff. Bad to the bone...

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-12 : 14:30:22

if they're all JPG's, it does work !

- Jeff

jgallen23
Starting Member

17 Posts

Posted - 2007-01-12 : 14:36:36

users upload the images and then they are resized, so I have no idea what file extension they are (jpg, gif, png)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-12 : 15:46:21

With JOLIET, it is not guaranteed that the last point is the fourth last character...

declare @s varchar(100)

select @s = '6fe7651b-2ee3-4aea-a0c4-a7a2689edea9/blah.jpg'

select	left(org, len(org) - lp) + '_small' + right(org, lp)
from	(
		select	@s org,
			charindex('.', reverse(@s)) lp
	) x

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-12 : 15:54:02

My advice: Your client app should use its superior string manipulation ability to break up the filename into 3 parts -- folder, filename, extension -- and you should store those 3 parts as 3 separate columns in your database. When your data is stored fully parsed, it can easily be manipulated going forward. In other words, manipulate the string *once* at entry, store it properly in parts, and then further manipulations and SQL statements are very, very easy to do.

- Jeff

evjo
Starting Member

20 Posts

Posted - 2007-01-12 : 18:14:25

I don't know your system or your full requirements, but perhaps CLR would be the best solution (I know this is the T-SQL section of the forum!), you could go to town using reg ex etc, but it could be over kill if the solution doesn't have to be too generic. At the very least it will be faster than an equivalent T-SQL solution.

Whenever I see an old lady slip and fall on a wet sidewalk, my first instinct is to laugh. But then I think, what if I was an ant, and she fell on me. Then it wouldn't seem quite so funny.

- Jack Handey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources