LEFT And String Function

Nov 3, 2006

select left('Hello World /Ok',charindex('/','Hello World /Ok')-1)
Hello World
That works fine.

However I got an error message:
select left('Hello World Ok',charindex('/','Hello World Ok')-1)
Instead of:
'Hello World Ok'

I get:
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

Microsoft Doc incorrectly says:
"LEFT ( character_expression , integer_expression )
integer_expression
Is a positive whole number. If integer_expression is negative, a null
string is returned."

Is there an easier solutoin using left or any other string function
instead of using a case statement?
Also, charindex('/','Hello World Ok') should return NULL instead 0 so
that we can use isnull function.
Thanks.

View 2 Replies


ADVERTISEMENT

Transact SQL :: Invalid Length Parameter Passed To LEFT Or SUB-STRING Function

Jul 22, 2015

when I am trying to perform below query,
 
  INSERT INTO EMPLOYEE
    SELECT TOP 100 *
 FROM EMPLOYEE_LANDING;

I am getting Invalid length parameter passed to the LEFT or SUBSTRING function.

View 3 Replies View Related

Left Function

Apr 7, 2008

i have a query

select message from ticket

but i want to only get the first 100 characters

select left(message,100) from ticket

is giving me an error - please advise?

View 3 Replies View Related

Left() And Right() Function In MS SQL Vs MS ACCESS

Jun 29, 2006

I normally use MS ACCESS vs MS SQL,, which has a left() and right()function. I need to use MS SQL for this project but I am not familiarwith it. I have read a few books, but can not figure out how to dothis. Please help.If I need to compare the first 4 letters of a field, with the firstfour letters of another field, how can I do this?Select field1, field2 FROM table1 Where left(field1,4)=left(field2,4)(MS SQL does not have left() and right() functions)Please help.In addition, I have a CSV file with data like 10.20, which I importinrto a numberic field. Unforunately the value gets changed to 10.It's seems to get rounded. How can I fix this.The import SQL I use is....BULK INSERT dbo.tableFROM 'c:MYDATA.CSVWITH(FIRSTROW = 1,FIELDTERMINATOR = ',',ROWTERMINATOR = '')Thank you in advance!!!

View 9 Replies View Related

Combining A Right And Left Function

Dec 6, 2007

How do you combine a left and a right function. I have tried different variation and none seemed to work. What I want to accomplish is 6 spaces to the right and 2 spaces to the left.

View 3 Replies View Related

No Left Function In Expression Builder?

Dec 22, 2006

Anyone know the reaon the Left function was left out of the list of string functions in the Expression Builder?

Danno

View 6 Replies View Related

Can't Get Left() Function To Work With A Variable.

Jan 14, 2008

I have not been able to find the answer as to why the LEFT() function doesn't see the variable as being a variable.
I originally thought it did not accept a variable as the first parameter, however the definition says it can be a variable.
Anyone knows why this isn't working?
This is how I have the code:

SELECT LEFT(@tpatdata, CHARINDEX('^', Alert1) -1)

the variable @tpatdata is the column name (tablename.Alert1), iif I rewrite it like this:

SELECT LEFT(tablename.Alert1, CHARINDEX('^', Alert1) -1) it works.

View 5 Replies View Related

SQL Server 2008 :: Left Function And CHARINDEX

Mar 30, 2015

SELECT LEFT(InstanceName,CHARINDEX(' ',InstanceName)-1)
FROM ServerDB

Returns the following error.

Invalid length parameter passed to the SUBSTRING function.

View 2 Replies View Related

Stuff Function Not Working With Left Or Right Join

Jan 1, 2015

I found something very strange...stufff function working with self join but not working with left or right join,. I have a table

**Id name**

1 samar

1 Harry

2 jack

I want the output as

**Id name**

1 samar Harry

2 jack

The below query works fine with self join

Select b.id, stuff ((select ` ` + a.name from #test a where a.id = b.id
for xml path (``)),1,1,``)

From #test b
Group by id

But when i do right join i get error _ invalid object name `b`. ....

Select b.id, stuff ((select ` ` + a.name from #test a
right join b on b.id = a.id for xml path (``),1,1,``)

From #test b
Group by id

View 4 Replies View Related

Performance Issue Using Left Or Substring Function

Oct 18, 2007

Hi,

I've tried the following query in SQL SERVER 2005, SQL Express and MACCESS.

select * from Table1 where drid in (SELECT DrID FROM Table2 WHERE (substring(PostalCode,1,3) IN ('B0E','B1P','B2H','B2Y','B3A','B3M','B4A','B4H','E1A','E1C','E1N','G0A', …)) and (substring(Telephone,1,3) IN ('204','250','306','403','416','418','450','506','514','519','604','613','705','780','807','819','902','905')))

The query is using two table. The first one Table1 is a table with user info. The second table Table2 has the info concerning a survey.

The Table1 containt approx. 6000 row and Table2 containt only 210 rows

The table structure from the different environment(MACCESS, SQL SERVER 2005, Sql Server Express 2005) are the same. The Table1 containt the field "PostalCode" and "Telephone".

When I execute this query on MACCESS and in SQL Server 2005 the result are approximately the same(Less than half second). But there a performance issue in Sql Express 2005. The query take an execution time between 7 and 9 secondes.

When I add a condition using a field from tblResponsePQ2Part1 ex: QA=1
like in the following query :
select * from Table1 where drid in (SELECT DrID FROM Table2
WHERE (QA = 1 substring(PostalCode,1,3) IN ('B0E','B1P','B2H','B2Y','B3A','B3M','B4A','B4H','E1A','E1C','E1N','G0A', …)) and (substring(Telephone,1,3) IN ('204','250','306','403','416','418','450','506','514','519','604','613','705','780','807','819','902','905')))
the query take an execution time of ~15 secondes!!!!

This issue only happen in Sql Server Express, on the others cases(mean MSAccess, Sql Server) the execution time is less than half second.

It’s weird because, Sql Express 2005 is supposed to be more performant
than MACCESS, and have the same performance than Sql Server Professional Edition. Please Help Me!!!!


Anyone have an idea why?



Mathieu Desbiens

View 1 Replies View Related

LEFT Function On Text-field (SQL Server Datatype)

Dec 6, 2004

Hello!

I am trying to run a query on an SQL-server database:

SELECT (Left(Comments, 20)) As shortComment FROM myTable

The Field Comments is a Text field (datatype is text), and I can't get the LEFT function to work with this datatype...

How can I bypass this problem? Can I convert the commentfield to varchar in the QUERY?


noccy

View 1 Replies View Related

Delete Right And Left Part And Show Middle String?

Oct 31, 2014

i have a column name remarks and i want to retrieve data like by deleting some of the leading charaters and some last characters and show the middle one.

like remarks has CALLTRANSFER_OVER_SIP:XfrTime=86.05599975585938_en and i want to show only "XfrTime=86.05599975585938" this much

View 3 Replies View Related

How To Return Partial String Using CharIndex And Left In Same Select

Jun 22, 2015

Select
left(
[Description],(charindex(';',[Description],1)-1))
from xxxx

Example of Description contains

Ankle Supports; Color=Black; Size=S

So I want the left side up to and NOT including the semi colon.

View 14 Replies View Related

SQL Server 2008 :: Using Left And Charindex To Parse String / Getting Rid Of Rest Of Data

Jun 16, 2015

I am trying to erase some erroneous bad data in my table. The description column has a lot of </div>oqwiroiweuru</a> weird data attached to it, i want to keep the data to the left of where the </div> erroneous data begins

update MyTable
set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid = 1

that totally works.

update MyTable
set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid >= 2

gives me a Invalid length parameter passed to the LEFT or SUBSTRING function. The statement has been terminated error.

View 2 Replies View Related

Transact SQL :: Left Pad With 0 And Extract 2 Characters On The Left?

Oct 28, 2015

I have a table PLACE with a character column CODE

[Code] [nchar](4) NULL

I need to left pad the column with 0 if it is less than 4 characters long and extract the first 2 characters on the left into a new column COUNTY_CODE.

How can I do that in transact SQL?

I tried:
     
SELECT  RIGHT(RTRIM('0000'+ISNULL([Code],'')),4)     
   FROM [Place]
   WHERE [Place Code]='B' and [Code]='627'

And I got 0627. And how do I extract the first 2 characters?

View 10 Replies View Related

Return Left-most Character From 8 Character String

Oct 1, 2014

I'd like to return the left-most character from an 8 character string & the third from the left character too.

Like this ABC00123 returns AC

$query = "SELECT LEFT(uninum,3), RIGHT(uninum,5), clmarea, Date FROM tblunimov";
$result = mysql_query($query) or die(mysql_error());

echo "<div class='tblstyle1'>";
echo "<table class='tblstyle1'>";
echo "<tr><th>ini</th><th>item</th><th>area</th><th>date</th></tr>";
while($row = mysql_fetch_array($result)){

[Code] ....

View 5 Replies View Related

STRING FUNCTION

May 14, 2001

Hi!

Is there any fucntion avaible in SQL for search a character position in a string.

e.g. 'ABC-DEF'

- is located at the position of 4

I tried the charIndex, Is it same as Instr in ORACLE. Is there any INSTR function available

View 1 Replies View Related

A String Function

Aug 25, 1999

does anyone know a function to count the number of times a substring appears in a string?
example NumberOfTimes("i","mississippi") would return 4

View 3 Replies View Related

Right And String Function

Jun 21, 2001

I need to take the character from a text field. I need the character which
is the second one from the end(right). Like out of '12345' or '99821'
I would need the 4 from the first and the 2 from the second. Can I combine
a string and a right statement to do this?
Thanks

View 4 Replies View Related

SQL String Function Help

Mar 13, 2006

Here is the scenario.

I have a 10 position varchar field that holds a project number. The project number is in the form yy-nnn... where yy is the current year (06) and nnn is a sequential number. The project number is displayed on a data entry form so that the next record has the next sequential number.

So when the data entry form first opens the project number field has 06-1 after that record is entered the form is cleared and reinitialized and the project number now shows 06-2. If the form is closed and then reopened the project number would still show 06-2 if that previous record was not entered and 06-3 if it was.

The way I am going about getting the next sequential number is the following:

SELECT MAX(RIGHT(ERFNumber, LEN(ERFNumber)-3)) AS MaxERFNumber From Table1

This approach is working fantastic until record 06-9 is entered. The above line does determine that 06-9 is the MAX record and then creates a record 06-10 but every subsequent time a record is attempted it still thinks 06-9 is the MAX value of the above statement.

I am assuming that since the field is defined as a varchar the MAX function is applying character logic versus numeric logic thus the 9 being treated as the MAX value.

Is there an SQL function that can change the result of RIGHT(ERFNumber, LEN(ERFNumber)-3) to a numeric value so that the MAX function will work correctly.

I know there are many other ways to do this but in the interest of time because the application is almost finished, changing this now is going to be a major undertaking.

Any help or guidence would be greatly appreciated.

View 5 Replies View Related

String Function

Jun 23, 2006

Hi all, iam trying to find a string function which would replace column value where there is a ssn with 1 and anything else(blank,null,...) with 0. i need to count the number of rows with ssn and one without ssn.
i checked few string functions but no use

any help appreciated

View 5 Replies View Related

SUM Function For String????

Aug 16, 2007

hi kristen or anybody there,

is there a way you can concat string columns in a select statement... i have two tables (joined) and i wanted to display a concatenated values.

data details:
table 1
pk_transactionid transactiondate and so on
1 1/1/2007 *********
2 1/2/2007 *********
3 1/3/2007 *********

table 2
pk_rowid fk_transactionid description
1 1 record1
2 2 record2
3 3 record3
4 1 record4
5 2 record5
6 1 record6

result set should be like this
pk_transactionid transactiondate description --> this is concatenated
1 1/1/2007 record1, record4, record6
2 1/2/2007 record2, record5
3 1/3/2007 record 6

thanks

SlayerS_`BoxeR` + [ReD]NaDa

View 12 Replies View Related

String Function

Dec 19, 2006

select distinct(f.agency+y.year+s.type) as SDNFIn this select clause, I try to use the last 2 digit of year for y.yearvalue. which function should i apply to it?

View 1 Replies View Related

String Function

Jan 23, 2008

Is there a way to convert special characters such ö to o?

Cheers

View 3 Replies View Related

String Function Needed

Sep 26, 2006

I have a table with a column called Userdef. I is a user defined field. It looks like this ;;Polk;D-0002;;;;As you can see it is delimited by semicolons.  I need to separate the semicolons into separate files like this  Field1Field2Field2Field4Field5Field6<null><null>Polk<null>D-0002<null>How do I write this query in SQL Server?  

View 1 Replies View Related

Is There AT-SQL Function Like VBScript STRING() ?

Aug 11, 2000

This function is useful for fixed width output. E.g.

String(expr,10-len(expr),'0') will give you a number with leading zeros so it is always 10 characters long.

Thanks in advance for your time.

View 1 Replies View Related

String Function Questions

Jan 10, 2001

Hello All,
I have am importing a file that contains an address field where the city and state are in the same field, IE ['Atlanta, GA']. How do I separate the city and state into separate fields. I can get the state by using the RIGHT function. My problem is getting the city information up to the comma.

Any help would be appreciated.

TIA

View 1 Replies View Related

String Function - InStr

Dec 11, 2002

In VB there is InStr, in Excel you have SEARCH. what function can i use to search a string for a specific string. ie: how can i find where a space is in someones name?

our database has a table that is used for security. it contains a user code, user name and passcode. i need to split the username (currently forename and surname in same field) into two, around the space.

in VB i would write something like
strFName = left(<usernamefield>,InStr(1,<usernamefield>," ")-1)
to get the forename and similar to get the surname. how can i do this in SQL so that a view will supply the data apprpriately?

one of the reasons i want to do this is so that i can sort the users by surname! another is so that i can give options on their usercode - a combination of forename initial and 2 characters from surname.

any help welcomed.

View 4 Replies View Related

SQL Search String Function

Jun 2, 2008

The 'LIKE' function looks for words that start with whatever is in the like condition. Is there an sql function similar but will look and compare at any part of the search string.

For example I am using a webservice in dot net to populate a dropdown list using this sql

SELECT compound_name FROM dbo.compound_name WHERE compound_name like @prefixText

In this table there is a compound called SILCAP310 and I would like the search function to pick up 310 if I put this into the @prefix parameter. (but I would still like the search to perform like the 'LIKE' does also.

SELECT compound_name FROM dbo.compound_name WHERE compound_name like @prefixText or compound_name SearchPartString @prefixText

Thanks in advance

View 4 Replies View Related

Function To Parse A String

Mar 13, 2008

Hope someone can help... I need a function to parse a string using a beginning character parameter and an ending character parameter and extract what is between them. For example.....

Here is the sample string: MFD-2fdr4.zip

CREATE FUNCTION Parse(String, '-' , '.')
.....
....
.....
END


and it shoud return 2fdr4

View 8 Replies View Related

FINDSTRING String Function

Sep 21, 2007

I'm having issues trying to build an expression using the FINDSTRING function. Even when I use an example from BOL, I get an error upon clicking the "evaluate expression" button within expression builder saying "Cannot convert system.int32 to system.string".

Here is my test expression...




Code SnippetFINDSTRING("ABC", "A", 1)

Here is the BOL expression...
Code SnippetFINDSTRING("New York, NY, NY", "NY", 1) Both give me the same error message. I even tried casting it to an integer with no luck. Any ideas?
Code Snippet(DT_I4) FINDSTRING("ABC" , "A" , 1)

View 4 Replies View Related

SQL &"left&" Function

Nov 14, 2005

I am using the following to display a short version of my description field:

<%=LEFT(rs("DESCRIPTION"),150)%>

The only problem is that it cuts at exactly 150 chars...so it will display a portion of a word.

Can any one help me change this so it will cut the record off at the last "space" before the 150 chars is up? So it doesn't chop the last word.

View 3 Replies View Related

MS SQL Function Return String - What Am I Doing Wrong?

Jun 6, 2005

Cannot see where I am going wrong. I always get a value of 0. I know my function works correctly, so it must be the VB.


CREATE FUNCTION [dbo].[getNextProjectID] ()
RETURNS varchar(10) AS
BEGIN
'''''''''''''''''''...........................
DECLARE @vNextProjectID varchar(10)
RETURN @vNextProjectID
END


Sub LoadNextProjectNumber()
        Dim vProjectID As String
        Dim cmd As New SqlClient.SqlCommand()
        cmd.Connection = sqlConn
        cmd.CommandText = "getNextProjectID"

        cmd.Parameters.Add("@vNextProjectID", vProjectID)
        cmd.Parameters("@vNextProjectID").Direction = ParameterDirection.ReturnValue

        cmd.ExecuteScalar()
        vProjectID = cmd.Parameters("@vNextProjectID").Value

        txtProjectID.Text = vProjectID

        cmd.Dispose()
End Sub

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved