LEN LEFT/SUBSTRING function mix? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

LEN LEFT/SUBSTRING function mix?

Author

Topic

redcairo
Starting Member

4 Posts

Posted - 2008-08-04 : 17:57:13

ref topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104086

1. I have a filename in the database, it is usually 18 characters long in a VARCHAR but there are some fields with no entry in this column and it's possible some are entered but different lengths.

2. I need to take off the extension, add a new extension (static 'jpg'), and add a string to the beginning ('mini_'). This is how the smallest thumbnails in my gallery are named, which I want to display in an output list. Since I'm using CFGRID I have to get the final value from SQL rather than using middleware to calculate it like I used to.

So 123456789ABCDE.gif(/png/jpg) needs to end up 'mini_123456789ABCDE.jpg'.

3. I figured out how to do string manipulation, how to get the length of the field, how to subtract a number from that length. That was the easy part. But:

When I try to use the 'generated column value of the length - 3' as a NUMBER in a LEFT or SUBSTRING statement, I get an error. Basically I was trying to use it like you would a variable in middleware, which isn't working.

I tried to do it like basic math:

A - get length of entry in filename column, gives newcol1

B - subtract 3 digits from that, gives newcol2 (it turns out A&B combined to one function)

C - go get (LEFT or SUBSTRING) just that newcol2 quantity of chars from the original filename column

D - now mix that with a string function to add the prefix and new extension.

From this thread I'm guessing it might be partly because not every record has a value in this field. But I can't change the data; the files are what they are (or are not, as the case might be). What then?

And is there any way to use a *generated value* in a LEFT or SUBSTRING function?

Those are the two main questions.

This code below does not work -- the last two lines of the SELECT don't work, anyway, I know that -- but I'm trying to example what I did and what my logic was.


SELECT myrecordID,

LEN(dsfbfile)  -4 as THENEWLENGTH,

LEFT(dsfbfile, THENEWLENGTH) as FILETRIM,

'mini_' + filetrim + 'jpg' AS minipic

Any help would be hugely appreciated.

Best,
PJ

redcairo
Starting Member

4 Posts

Posted - 2008-08-04 : 18:06:01

Since a filename would have a period in it, I'm wondering if this function I just found a reference to would work.

PARSENAME retrieves parts of string delimited by dots. 
It is used to split DataBaseServer, DataBaseName, ObjectOwner and ObjectName 
but you can use it to split IP addresses, names etc.

DECLARE @ParseString VARCHAR(100)
SELECT @ParseString = 'DataBaseServer.DataBaseName.ObjectOwner.ObjectName'
SELECT PARSENAME(@ParseString,4),

(got from http://dotnetsamplechapters.blogspot.com/2007/09/ten-sql-server-functions-that-you.html)

I don't know how to use it but it seems like a potential in this case.

redcairo
Starting Member

4 Posts

Posted - 2008-08-04 : 18:21:39

I figured it out (am quite pleased with myself -- I'm still learning SQL).

SELECT myrecordid, myfilename as longfile,
CASE 
 WHEN
  LEN(myfilename) > 5
    THEN 'mini_' + LEFT(dsfbfile,CHARINDEX('.',myfilename)-1) + '.jpg'
  ELSE
    'nofilehere.gif'
  END
    AS shortfile

Here's the result:

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources