Is their a way to return only a portion of a string returned from a query? Please look at the following query and what it Yields: select UserName from MyDirectory NTSRJoe MeekletonNTSRAnfreesa ClinklHeadBRYAmy KooplosaAKVermian Strikoos How can I fix this query so that it returns everything to the right of the '' character? So I want the query to instead return the following: Joe MeekletonAnfreesa ClinklHeadAmy KooplosaVermian Strikoos
I want to return the character(s) in a string directly after a dash (-) character. For example in the following numbers 72187-1 & 72187-2 I am interested in returning 1 & 2 respectively.
It is possible in my scenario that the number after the dash is two digits long, e.g.: 72187-12 in which case I want to return 12.
I want to return the character(s) in a string directly after a dash (-) character. For example in the following numbers 72187-1 & 72187-2 I am interested in returning 1 & 2 respectively.It is possible in my scenario that the number after the dash is two digits long, e.g.: 72187-12 in which case I want to return 12.
Can anybody tell me how the character of hard return expressed in SQL Server. For example if I wanna convert any hard return character in a varchar field to something else, like a space, how could I write Replace function? Or if I wanna search any hard return character, how could I write where clause?
I am trying to insert a row into a table of Microsoft SQL Server 2000.
There are various columns.
[SNO] [numeric](3, 0) NOT NULL , [DATT] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DATTA] [char] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [CODECS] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
The [DATTA] column is causing a problem. Even if I am trying to put only 1700 character string into [DATTA], the java code throws the following exception:-
StaleConnecti A CONM7007I: Mapping the following SQLException, with ErrorCode 0 and SQLState 08S01, to a StaleConnectionException: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
Why is it throwing an exception even though the sum-total of this row doesn't exceed 8000 characters?
I am currently doing a mapping from text file to sql table. there is one column in the text file that contain more than 15,000 characters in it. it causes me an error because i was using varchar data type, in which i believe can only store 8000 char. can anybody tell me which data type to use? and also, will i be able to change the data type in the input column also? if yes, which data type should i change it to?
I had to import some FileMaker Data into SQL Server 2005 and in the resume field a "SQUARE" special character is appearing everyplace that a return should be.
It has really messed up the formatting. I know how to use the updatetext in a cursor to replace the special character but I don't know what to replace the "SQUARE" special character with so the text is displaying a new line rather then this "SQUARE" character.
I have a table like this below and it doesn't only contain English Names but it also contain Chinese Name. CREATE TABLE Names (FirstName NVARCHAR (50), LastName NVARCHAR (50)); I tried to view the column using SQL Query Analyzer, It didn't display Chinese Character. I know that SQL Server 2005 is using UCS-2 Encoding and Chinese Character uses Double Byte Character Set (DBCS) Encoding. I want to read the FirstName and LastName columns and display in Window Form Data Grid and ASP.NET Grid View. I tried to use this code below and it didn't work. It convert some of the English Name to Chinese Character and it display the chinese character and some still in the original unreadable characters. Does anybody know how to read those character from SQL Table and display the correct Chinese Character without converting the English Name into Chinese also? Thanks
int codePage = 950; StringBuilder message = new StringBuilder(); Encoding targetEncoding = Encoding.GetEncoding(codePage); byte[] encodedChars= targetEncoding.GetBytes(str); . message.AppendLine("Byte representation of '" + str + "' in Code Page '" + codePage + "':"); for (int i = 0; i < encodedChars.Length; i++) { message.Append("Byte " + i + ": " + encodedChars); }
message.AppendLine(" RESULT : " + System.Text.Encoding.Unicode.GetString(encodedChars)); Console.Writeline(message.ToString());
I want to read a String - character by character.I mean If the string is 'SAMPLE' then I want to go to each and every character in it 'S','A','M','P','L','E' to compare with another string for equality. I hope I am clear.Is there a way that I can read it?
I'm running into a couple of performance issues with regards to the parsing of a text string. We have a function that will take a comma delimited character string, parse out the individual values, and then populate a temp table with those values. The two issues are 1.) the parsing process is VERY slow and 2.) there's a max to how large the string can be - at some point it could easily be 8000 characters or more in length.
Here are the function and the stored procedure wher eit occurs:
CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))
RETURNS @Results TABLE (Item nvarchar(4000))
AS
BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
-- ERO FIRST TIME IN LOOP
SELECT @INDEX = 1
WHILE @INDEX !=0
BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
Basically I need to insert underscore in between a string when there is a space in the content of the string. For Example say we have string 'ABC XYZ', I've to convert it into something like this 'ABC_XYZ'. Some strings do not have space in between and I should not add underscore in such cases. I'm working with MSSQL Server 2008 version.
I simply need to separate the string for each row by the & character and then I'm assuming i ll be able to COUNT and GROUP BY the occurrences of each separate value in order to find the most commonly used inputs.I have a column Variables in the table Functions, that contains a string of values separated by the & character that shows the inputs each student inserted into a function.
How would I go about splitting that string without the use of a function or stored procedure and the find the most commonly used variables? (I was thinking the latter part could be easily solved with a COUNT(*) and appropriate GROUP BY.)
In a column I have some values for part names. The 6th character tellsyou where the part came from, and this is the same scheme for everysingle part in the database.If I want to do something like return the basic name of a given part,without the factory identifier character, I need to replace thatcharacter with a '_' character. (So for instance '11256CA' and'11265AA' and '11256MA' would all just get turned into '11256_A' andonly one row would be returned in the SELECT DISTINCT statement)I know how to replace an instance of a given character using replace(),but how can I alter a specific character in a string if all I know isthe index of the character within the string?TIA,-CS
I need to creat distinct terms of the example parsing the term on the '|' character. I will be using mysql.
example: 1885-1974.|Johnson family|Frontier and pioneer life - Alberta - Black Hill district|Cadogan region (Alta.) - Biography|Black Hill district (Alta.) - Biography
Hello, In an SSIS package, I am attempting to copy data from a source where I am using the .NET provider for ODBC. The destination is a SQL Server 2005 table, and the connection uses the OLE DB provider for SQL Server.
On occasion, the data in a column called ADDR1 can contain a character at the end of the string that comes across in appearance as a box (when observed from a UTF-8 encoded text file). When this occurs, the record makes its way to the error output. I've tried using RTRIM and TRIM, and a couple of things with REPLACE in a Derived Column transform, but I can't seem to eliminate this extra character.
If I don't configure the error output, then the package fails on a truncation error on the ADDR1 column. The error output shows that the physical number of characters in the string never excedes the allowable character length (30).
I'm running into a couple of performance issues with regards to the parsing of a text string. We have a function that will take a comma delimited character string, parse out the individual values, and then populate a temp table with those values. The two issues are 1.) the parsing process is VERY slow and 2.) there's a max to how large the string can be - at some point it could easily be 8000 characters or more in length.
Here are the function and the stored procedure where it occurs:
CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1)) RETURNS @Results TABLE (Item nvarchar(4000)) AS BEGIN DECLARE @INDEX INT DECLARE @SLICE nvarchar(4000) -- HAVE TO SET TO 1 SO IT DOESNT EQUAL ZERO -- FIRST TIME IN LOOP SELECT @INDEX = 1 WHILE @INDEX !=0 BEGIN -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER SELECT @INDEX = CHARINDEX(@Delimiter,@STRING) -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE IF @INDEX !=0 SELECT @SLICE = LEFT(@STRING,@INDEX - 1) ELSE SELECT @SLICE = @STRING -- PUT THE ITEM INTO THE RESULTS SET INSERT INTO @Results(Item) VALUES(@SLICE) -- CHOP THE ITEM REMOVED OFF THE MAIN STRING SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX) -- BREAK OUT IF WE ARE DONE IF LEN(@STRING) = 0 BREAK END RETURN END
--------------------
...and the stored procedure:
CREATE PROCEDURE [dbo].[RPTPatientAnalysis] ( @stateList CHAR(2), @employerIdList VARCHAR(4000), @payerIdList VARCHAR(4000) ) AS SELECT p.PAT_ID, p.PAT_FirstName, ISNULL(p.PAT_MiddleName,'') AS PAT_MiddleName, p.PAT_LastName, p.PAT_Gender, CONVERT(VARCHAR(10),p.PAT_DOB,101) AS DOB, p.PAT_AddressStreet1, ISNULL(p.PAT_AddressStreet2,'') AS PAT_AddressStreet2, p.PAT_AddressCity, p.PAT_AddressStateProvince, p.PAT_AddressPostalCode, ISNULL(p.PAT_EmailAddress,'') AS PAT_EmailAddress, p.PAT_PhoneNumber, ISNULL(e.EMPLOYER_Name,'<Unknown>') AS EMPLOYER_Name, ISNULL(p.PAT_OtherEmployerName,'') AS PAT_OtherEmployerName, ISNULL(p.PAT_Comment,'') AS PAT_Comment, ISNULL(p.PAT_PrimCareProv_PRIMCP_ID,'') AS PAT_PrimCareProv_PRIMCP_ID, ISNULL(p.PAT_PrimCareProvAllowNotification,0) AS PAT_PrimCareProvAllowNotification, ISNULL(p.PAT_PrimCareProvFullName,'') AS PAT_PrimCareProvFullName, ISNULL(p.PAT_DoNotMail,0) AS PAT_DoNotMail, ISNULL(p.PAT_UnderAgePermission,0) AS PAT_UnderAgePermission, p.PAT_LastEandMCodingDateTime, p.PAT_Desceased, p.PAT_PCP_ID, p.PAT_LastUpdatedDateTime, ISNULL(p.PAT_PCPRecordType,0) AS PAT_PCPRecordType, ISNULL(p.PAT_EnableEmailMarketing,0) AS PAT_EnableEmailMarketing, ISNULL(p.PAT_EnablePortal,0) AS PAT_EnablePortal, ISNULL(p.PAT_PortalID,0) AS PAT_PortalID, ISNULL(e2.EMPLOYER_Name,'') AS EMPLOYER_Name, ISNULL(p.PAT_OtherEmployerName,'') AS PAT_OtherEmployerName, pcp.PRIMCP_ID, ISNULL(pcp.PRIMCP_ADDR_ID,'') AS PRIMCP_ADDR_ID, ISNULL(pcp.PRIMCP_ClinicName,'') AS PRIMCP_ClinicName, ISNULL(pcp.PRIMCP_PhysicianFullname,'') AS PRIMCP_PhysicianFullname, pcp.PRIMCP_DateDeactivated, ISNULL(pcp.PRIMCP_Phone_MedicalRecordFax,'') AS PRIMCP_Phone_MedicalRecordFax, ISNULL(pcp.PRIMCP_Phone_Voice,'') AS PRIMCP_Phone_Voice, ISNULL(pcp.PRIMCP_MedicalRecords_Street1,'') AS PRIMCP_MedicalRecords_Street1, ISNULL(pcp.PRIMCP_MedicalRecords_Street2,'') AS PRIMCP_MedicalRecords_Street2, ISNULL(pcp.PRIMCP_MedicalRecords_City,'') AS PRIMCP_MedicalRecords_City, ISNULL(pcp.PRIMCP_MedicalRecords_State,'') AS PRIMCP_MedicalRecords_State, ISNULL(pcp.PRIMCP_MedicalRecords_Zip,'') AS PRIMCP_MedicalRecords_Zip, ISNULL(pcp.PRIMCP_Street1,'') AS PRIMCP_Street1, ISNULL(pcp.PRIMCP_Street2,'') AS PRIMCP_Street2, ISNULL(pcp.PRIMCP_City,'') AS PRIMCP_City, ISNULL(pcp.PRIMCP_State,'') AS PRIMCP_State, ISNULL(pcp.PRIMCP_Zip,'') AS PRIMCP_Zip, ISNULL(pcp.PRIMCP_DoNotFax,0) AS PRIMCP_DoNotFax, pati.PATINS_InsuranceTypeID, ISNULL(pati.PATINS_Account,'') AS PATINS_Account, ISNULL(pati.PATINS_Group,'') AS PATINS_Group, ISNULL(pati.PATINS_CopayType,'') AS PATINS_CopayType, ISNULL(pati.PATINS_CopayAmount,0) AS PATINS_CopayAmount, ISNULL(pati.PATINS_CollectFullAmount,0) AS PATINS_CollectFullAmount, ISNULL(pati.PATINS_EmployerPays,0) AS PATINS_EmployerPays, ISNULL(pati.PATINS_ZeroScreenCopay,0) AS PATINS_ZeroScreenCopay, ISNULL(pati.PATINS_ZeroVaccineCopay,0) AS PATINS_ZeroVaccineCopay, ISNULL(pati.PATINS_NonPar,0) AS PATINS_NonPar, ISNULL(pati.PATINS_MedicarePlan,0) AS PATINS_MedicarePlan, ISNULL(ipcl.INSPCAT_Description,'') AS INSPCAT_Description, ISNULL(ip.INSP_Name,'') AS INSP_Name, ISNULL(ip.INSP_ChargeFullPrice,0) AS INSP_ChargeFullPrice, ISNULL(ip.INSP_CopayApplies,0) AS INSP_CopayApplies, CONVERT(VARCHAR(10),ip.INSP_DeactivatedDate,101) AS INSP_DeactivatedDate, ISNULL(ip.INSP_EligibilityActive,0) AS INSP_EligibilityActive, CONVERT(VARCHAR(10),ip.INSP_PromoStartDate,101) AS INSP_PromoStartDate, CONVERT(VARCHAR(10),ip.INSP_PromoEndDate,101) AS INSP_PromoEndDate FROM dbo.patient AS p LEFT JOIN dbo.Employer AS e ON p.PAT_EMPLOYER_ID = e.EMPLOYER_ID LEFT JOIN dbo.Employer AS e2 ON p.PAT_SecondaryEMPLOYER_ID = e2.EMPLOYER_ID LEFT JOIN dbo.PrimaryCareProvider AS pcp ON p.PAT_PCP_ID = pcp.PRIMCP_ID LEFT JOIN dbo.PatientInsurance AS pati ON p.PAT_ID = pati.PATINS_PAT_PERS_ID AND PATINS_InsuranceTypeID = 1 LEFT JOIN dbo.InsurancePayer AS ip ON pati.PATINS_INSP_ID = ip.INSP_ID LEFT JOIN dbo.InsurancePayerCategoryLookup AS ipcl ON ip.INSP_INSPCAT_ID = ipcl.INSPCAT_ID WHERE p.PAT_AddressStateProvince IN (SELECT Item FROM dbo.SplitVarcharMax(@stateList,',')) AND PAT_EMPLOYER_ID IN (SELECT Item FROM dbo.SplitVarcharMax(@employerIdList,',')) AND pati.PATINS_INSP_ID IN (SELECT Item FROM dbo.SplitVarcharMax(@payerIdList,','))
Is there a faster / more efficient way to accomplish the above?
hi all, i'm retreiving user input using textboxes and saving to a gridview. i'm getting this error and i dont know whats causing it. <asp:SqlDataSource ID="SqlDataSource1" runat="server" InsertCommand="INSERT INTO test101(Surname,Names,Regno)VALUES (@Surname, @Names, @Regno)"ConnectionString="<%$ ConnectionStrings:engineeringConnectionString %>" ProviderName=System.Data.SqlClient ConflictDetection="CompareAllValues" >
Dim CityTown As String = Ctype(Request.Querystring("CityTown"), String) Dim Suburb As String = Ctype(Request.Querystring("Suburb"), String) Dim SuburbValue As String = Ctype(Request.Querystring("Suburb"), String) Dim Rooms As String = Ctype(Request.Querystring("Rooms"), String) Dim Rent As String = Ctype(Request.Querystring("Rent"), String)
Dim DateToday = DateTime.Now Dim mySQL AS String If suburbValue = "- All -" Then mySQL = "SELECT propListID, propListExpires, propBuildType, propRoomNumber, propRentPerWeek, propDescription, propCityTown, propSuburb FROM tblPropertyList WHERE [propCityTown]='" & CityTown & "' AND [propRentPerWeek]<= '" & Rent & "' AND [propRoomNumber]='" & Rooms & "'AND [propListExpires] >='" & DateToday & "' "
However I am having trouble getting the Date to work as part of my SQL String. I keep getting the following error:
Syntax error converting datetime from character string. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Syntax error converting datetime from character string.
Source Error:
Line 30: MyDA = New SqlDataAdapter (mySQL, myConn) Line 31: myDS = New DataSet() Line 32: myDA.Fill(MyDS) Line 33: Line 34: dlPropertyListing.Datasource = MyDS.Tables(0)
Hi, I've got a situation where a table column is composed of decimal numbersthat have to be converted to whole numbers. I want to use CEILING and FLOOR functions to round them up or down, but how do I extract the part after the decimal point to evaluate which operation to apply? I don't think there is a function in sql server that looks for a position of a character within a string. I tried INSTR (the vb function) and that gave me an error msg.
