Trying to get a max value of a substring

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)

Trying to get a max value of a substring

Author

Topic

afitch
Starting Member

7 Posts

Posted - 2008-07-02 : 20:24:35

I have a field that I want to try and get the max value for a substring of a column. For example I have a number, I know that I have a 6 char string prepended to teh value, but want to get the maximum value of the remaining chars.
I thought the query would go something like
select max(substring(field, 6, Len(field) - 6)) from table, but this does not produce anything resembling the result I'm looking for. Does anyone have any pointers for how to do something like this?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-02 : 21:11:51

Can you post some sample data with expected result?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-03 : 02:28:56

if you have 6 char string prepending it, shouldnt you be starting from 7th postion?

ie. select max(substring(field, 7, Len(field) - 6))

geraldjr30
Starting Member

5 Posts

Posted - 2014-06-10 : 16:52:22

hi. i think the error has something to do with the charindex. i read something about adding ISNULL and NULLIF. can someone please advise how i would change the query by adding this?

getting a Msg 536, Level 16, State 5, Line 3
Invalid length parameter passed to the SUBSTRING function. error. here is part of the query:

SELECT top 100000

...
,LEFT(LTRIM(SubString(RMS.Nm12,
CHARINDEX(' ', RMS.Nm12)+1,
len(RMS.Nm12) -
CHARINDEX(' ', RMS.Nm12))),
CHARINDEX(' ', LTRIM(SubString(RMS.Nm12,
CHARINDEX(' ', RMS.Nm12)+1,
len(RMS.Nm12) -
CHARINDEX(' ', RMS.Nm12)))))
AS FIRST

,right
(LTRIM(SubString(RMS.Nm12,
CHARINDEX(' ', RMS.Nm12)+1,
len(RMS.Nm12) -
CHARINDEX(' ', RMS.Nm12))), --FIRST TEMP
len(LTRIM(SubString(RMS.Nm12,
CHARINDEX(' ', RMS.Nm12)+1,
len(RMS.Nm12) -
CHARINDEX(' ', RMS.Nm12)))) -
CHARINDEX(' ', LTRIM(SubString(RMS.Nm12,
CHARINDEX(' ', RMS.Nm12)+1,
len(RMS.Nm12) -
CHARINDEX(' ', RMS.Nm12))))
) AS [MIDDLE]

,LEFT(RMS.Nm12,
CHARINDEX(' ', RMS.Nm12))
AS LAST

"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-06-21 : 06:10:43

can i ask what exactly you're trying to achieve using the query above?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources