LEN Function - 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

General SQL Server Forums

New to SQL Server Programming

LEN Function

Author

Topic

directis5451
Starting Member

6 Posts

Posted - 2008-06-03 : 14:43:55

Sorry people but I am really new to SQL server, but here goes.

I have the folowing database using the table MasterCalendar.

I would like to drop the last 3 caracters off the cohort field and insert into a new field. When I use the following code I can make the LEN() work and also make the SUBSTRING() work but when I try to combine into 1 function I get an error message.

This works as expected
USE FacultyServices_1
Select Cohort, LEN(Cohort)-3 AS L_Cohort,
//this returns a number depending on length
SUBSTRING(Cohort,1,5)AS SUB_cohort
//this returns the 1st 5 characters of the cohort
From MasterCalendar

Expected results
<edit by tkizer>removed image since it's asking us for login information</edit>

This doesn't work!
USE FacultyServices_1
Select Cohort, LEN(Cohort)-3 AS L_Cohort,
SUBSTRING(Cohort,1,LEN(Cohort)-3)AS SUB_cohort
From MasterCalendar

Notice I substituted the LEN(cohort)- 3 function into the
SUBSTRING(Cohort,1,5) AS SUB_cohort
changing the the number 5 to
LEN(Cohort)-3

This kind of logic works well in Excel or Access but I get this error
in SQL 2005
Msg 536, Level 16, State 5, Line 2
Invalid length parameter passed to the SUBSTRING function.

All I really want to do is drop the last 3 characters off a variable length field returning the remaing characters.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-03 : 14:47:20

Do you have data that is 3 characters or less in that column?

By the way, your image isn't working as it's asking us to login. I've edited your post due to this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

directis5451
Starting Member

6 Posts

Posted - 2008-06-03 : 15:04:11

Thanks for the response, The Cohort column did contain 2 null values, they have been deleted, all other entries are 7 characters or more.

Sorry about the image, Don't know what to do or how to change it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-03 : 15:07:03

Are you getting the error after you deleted those 2 rows?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

directis5451
Starting Member

6 Posts

Posted - 2008-06-03 : 15:10:25

Yes, the error still exists,

Msg 536, Level 16, State 5, Line 2
Invalid length parameter passed to the SUBSTRING function.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-03 : 15:18:55

It works fine for me, perhaps you need to provide a data example. Here is my test:

DECLARE @s varchar(10)

SET @s = 'abcdefghij'

SELECT LEN(@s)-3 AS L_Cohort, SUBSTRING(@s,1,LEN(@s)-3)AS SUB_cohort,
LEFT(@s, DATALENGTH(@s) - 3) AS Tara

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

directis5451
Starting Member

6 Posts

Posted - 2008-06-03 : 15:26:27

Here's an interesting development, if I change the LEN(Cohort)-3)to
LEN(Cohort)-2)it works fine if I change it to LEN(Cohort)-1)it works fine, 3 or higher doesn't work even though the Least amount of characters is 7

This works
USE FacultyServices_1
Select Cohort, LEN(Cohort)-3 AS L_Cohort,
SUBSTRING(Cohort,1,LEN(Cohort)-2)AS SUB_cohort
From MasterCalendar

returns this
Cohort L_Cohort SUB_Cohort
AALA024 4 AALAO
AALA025 4 AALA0
And so on

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-03 : 15:30:57

What does this return:

SELECT MIN(DATALENGTH(Cohort)) AS MinDataLenCohort, MIN(LEN(Cohort)) AS MinLenCohort
FROM MasterCalendar

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

directis5451
Starting Member

6 Posts

Posted - 2008-06-03 : 15:39:31

Hi Tara, Thank you so much for your help, I just added the ORDER BY L_Cohort and as you expected there are enteries of only 2 characters.

Go figure, scrolling isn't all that accurate.

Thanks again

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-03 : 15:43:24

SELECT CASE WHEN DATALENGTH(Cohort) < 4 THEN Cohort ELSE LEFT(Cohort, DATALENGTH(Cohort) - 3) END
FROM MasterCalendar

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

directis5451
Starting Member

6 Posts

Posted - 2008-06-03 : 15:49:52

Works like a charm, problem solved. Thanks again

redcairo
Starting Member

4 Posts

Posted - 2008-08-04 : 17:32:40

This was really interesting, thanks for sharing the above code.

I found this thread on a search because I'm trying to do something similar... yet different. I can't seem to figure out how, though I admit I'm no expert.

1. I have a filename in the database, it is usually 18 characters long in a VARCHAR (SQL Server) but there are some fields with no entry in this column and it's possible some are 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 are named. So 123456789ABCDE.gif needs to end up looking like 'mini_123456789ABCDE.jpg' as a new column value.

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. 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.

In other words 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 (actually it turns out A&B combined to one function just fine)
C - now go get (LEFT or SUBSTRING) just that newcol2 quantity of chars from original filename column
D - mix it with some 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?

I'm using CFGRID to output to my website, so I have to get final values from my queries, rather than handling it through CF logic like I used to. (CFGRID is a beautiful pain in the butt that is demanding I greatly improve my SQL skills, mostly... :-))

I'd really appreciate any help. My coding is only for projects I have online free for users, nights/weekends when I have time, so I don't have any coworkers etc. to hit on for help when I'm stuck. :-)

PJ

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-04 : 17:34:49

redcairo, please start a new thread for your issue (you an reference this one in it) and provide sample data that illustrates your issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources