April 29, 2012 at 8:40 am
I want to remove last 12 characters of a string but looks like there is a limitation for only 8 charcters.
when i do select left(name,DATALENGTH(name1)-12) from #t
I get this error
Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
April 29, 2012 at 9:19 am
This may be your problem
CREATE TABLE #T (Name1 VARCHAR(20))
INSERT INTO #T
SELECT 'ABCDEFGHIJKLMNOP' UNION ALL
SELECT 'ABCDEFGHIJKL' UNION ALL --12 characters
SELECT 'XYZ'
SELECT DATALENGTH(Name1) AS 'Datalenth' FROM #T
Returned:
Datalength
16
12
3
For the two shorter entries you will be using in the SUBSTRING function either a 0 (zero) or a negative value.
Now this might be what you need
SELECT REVERSE(right(REVERSE(Name1),12)) from #t
Returns:
ABCDEFGHIJKL
ABCDEFGHIJKL
XYZ
Tara-1044200 Next time you post to a forum requesting assistance, please, please supply table definition(s), some sample data and expected/required results.
You can do this quickly and easilyt. Click on the first link in my signature block and read the article, which contains sample T-SQL code that you can use to make it easier for those who want to assist you with a tested solution.
April 29, 2012 at 11:19 am
actaully there isnt any table in real, i am just trying to use len(column)-12 which seems to be not working but it works for len(column)-8 so i was thinking how to get a result eliminating last 12 characters. do we have to use any other function as len does not work?
April 29, 2012 at 11:29 am
Tara-1044200 (4/29/2012)
actaully there isnt any table in real, i am just trying to use len(column)-12 which seems to be not working but it works for len(column)-8 so i was thinking how to get a result eliminating last 12 characters. do we have to use any other function as len does not work?
I disagree. Len() does 'work'. See this:
declare @x varchar(25), @y varchar(25)
set @x = 'abcdefghijklmnopqrstuvwxyz'
set @y = left(@x, len(@x)-12)
select @x, @y
Now perhaps you would post your code which proves it does not?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 29, 2012 at 11:47 am
Phil Parkin Posted Today @ 1:29 PM
Now perhaps you would post your code which proves it does not?
Here is a sample: I guessed what Tara-1044200 was attempting to do.
CREATE TABLE #T (Name1 VARCHAR(20))
INSERT INTO #T
SELECT 'ABCDEFGHIJKLMNOP' UNION ALL
SELECT 'ABCDEFGHIJKL' UNION ALL --12 characters
SELECT 'XYZ'
select left(name,DATALENGTH(name1)-12) from #t
Result:
Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
Using your code:
declare @x varchar(25), @y varchar(25)
set @x = 'abcdefghijk' -- 11 characters
set @y = left(@x, len(@x)-12)
select @x AS '@x', @y AS '@y'
Result:
Msg 536, Level 16, State 5, Line 3
April 29, 2012 at 12:05 pm
Here is a sample: I guessed what Tara-1044200 was attempting to do.
Indeed & a fair guess. But her post immediately after your suggestion seemed to indicate that things were still not 'working' - I assumed that she had read what you said and still had a problem. The detective in me was piqued.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 29, 2012 at 12:11 pm
Phil Parkin (4/29/2012)
Here is a sample: I guessed what Tara-1044200 was attempting to do.
Indeed & a fair guess. But her post immediately after your suggestion seemed to indicate that things were still not 'working' - I assumed that she had read what you said and still had a problem. The detective in me was piqued.
Ah yes ... remember mine was only a guess as well. Now the correct answer that should satisfy both you and I is (Drum roll, blare of trumpets) . The answer is:
It depends
April 29, 2012 at 12:33 pm
Not sure if understood you guys correctly but are you saying that i can remove last 12 charcters using LEN function?
i am just trying s simple select stmt and it does not work.
select left(name,len(name)-12) from master.sys.databases
April 29, 2012 at 12:45 pm
Tara-1044200 (4/29/2012)
Not sure if understood you guys correctly but are you saying that i can remove last 12 charcters using LEN function?i am just trying s simple select stmt and it does not work.
select left(name,len(name)-12) from master.sys.databases
Tara, what is the exact error you're getting?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 29, 2012 at 12:49 pm
Tara-1044200 (4/29/2012)
Not sure if understood you guys correctly ?
It depends
I used a table to illustrate what could/would happen when using just the LEN function. Please review what I originally posted and if you have any further question(s) about how it works, ask them ...but please be specific in posting sample data, and required results, so that we do not have to guess what output is required.
April 29, 2012 at 2:14 pm
I am getting the error
Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
Not sure what examples or sample data you are looking for when i am just trying to eliminate the last 12 characters of database name from sys.databases. I am sure every sql server would have the sys.databases to qry, let me know what else you need to check this.
April 29, 2012 at 2:16 pm
Tara-1044200 (4/29/2012)
Not sure if understood you guys correctly but are you saying that i can remove last 12 charcters using LEN function?
Sure you can. But you need to ensure that the strings you're trying to trim are more than 12 characters in lenght. Trying to remove 12 characters from a string that's only 8 characters in length will give you an error, you can't remove more characters than the string has, it's not logical and it can't produce a sensible result
i am just trying s simple select stmt and it does not work.
select left(name,len(name)-12) from master.sys.databases
Because you're not checking and ensuring that the names are more than 12 characters
select left(name,len(name)-12) from master.sys.databases
WHERE LEN(name) >= 12
Works fine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 29, 2012 at 2:18 pm
Tara-1044200 (4/29/2012)
I am getting the errorMsg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
Because you're trying to remove more characters than the string has. If you ask SQL to remove 12 characters from a string that is 5 characters long, what other than an error could SQL logically return?
Think about it, if you run that query on the database named model, this is what you're asking.
SELECT LEFT('model',5-12)
ie
SELECT LEFT('model',-7)
You cannot possibly get -7 characters from a string, there is no result other than an error which could be logically returned
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 29, 2012 at 2:21 pm
GilaMonster (4/29/2012)
Tara-1044200 (4/29/2012)
I am getting the errorMsg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
Because you're trying to remove more characters than the string has. If you ask SQL to remove 12 characters from a string that is 5 characters long, what other than an error could SQL logically return?
Heh, beat me to it. 🙂 You end up with a negative in the left piece such as -7 if you have 5 characters and attempt to remove 12, thus the error. Something in your data is a lot shorter than you expect it to be.
To quickly find them, SELECT [name] FROM table WHERE LEN([name]) < 12.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 29, 2012 at 2:26 pm
Gila,you got it. I forgot the where cluse, Thanks so much.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply