SELECT Character In A String (masking)
Aug 15, 2007
Anyone who knows a smarter way to select a special part of a text string.
A have done like this now but mabye the textstring changes. I know that the GO02 in the future will expand.
KnowgoodP_GO02_AA_K_20070807_2010_L.BBB
But the underscore _ sign is like a seperator and will always be there to separate the words.
I need the 20070807_2010 and fomat to 2007-08-07 20:10
SELECT @state = STATE,
@thetime = CONVERT(char(20),STATE_TIME,20),
@id = ID,
@textstrr = TEXTSTRRWHERE SUBSTRING(FILENAME, 21,4)+ '-'+ SUBSTRING(FILENAME, 25,2)+'-'+SUBSTRING(FILENAME, 27,2)+' '+ SUBSTRING(FILENAME, 30,2)+ ':'+ SUBSTRING(FILENAME, 32,2)+':00' between @starttime and @theEndTime
View 8 Replies
ADVERTISEMENT
Apr 15, 2004
I have data in a column that starts with 1-4 characters followed by a dash then followed by an number of characters (ex: EU-Surgery).
How do I select everything to the right of the dash when the number of characters to the left of the dash varies?
View 3 Replies
View Related
Sep 4, 2015
I'm trying to select only July from show_held but I keep on getting the error message saying:
Conversion failed when converting date and/or time from character string.
I get error message after I write this code:
ANDshow.show_held = '&July&'
As you can see from the below code, How do I select July from times_held_in_July?
SELECTevent_name,
DATENAME (MONTH, show_held) AS times_held_in_July
FROMevent,
show
WHEREevent.show_id = show.show_id
Result:
event_name times_held_in_July
DressageJuly
Jumping July
Led in July
Led in September
Led in May
DressageApril
DressageJuly
Flag and PoleJuly
SELECTevent_name,
DATENAME (MONTH, show_held) AS times_held_in_July
FROMevent,
show
WHEREevent.show_id = show.show_id
ANDshow.show_held = '&July&'
Result:
Msg 241, Level 16, State 1, Line 24
Conversion failed when converting date and/or time from character string.
View 6 Replies
View Related
Apr 30, 2008
Good day all.
Let's see if I can stump you with this one.....
Code Snippet
SELECT Account.New_DistributorAccountNumber, Account.New_NameonBill, ActivityPointer.Subject, ActivityPointer.CreatedOn AS ExprX
FROM Account INNER JOIN
ActivityPointer ON Account.AccountId = ActivityPointer.RegardingObjectId
UNION
SELECT Account_1.New_DistributorAccountNumber AS Expr1, Account_1.New_NameonBill AS Expr2, ActivityPointer_1.CreatedOn AS Expr19,
ActivityPointer_1.Subject AS Expr20
FROM Account AS Account_1 INNER JOIN
Contact AS Contact_1 ON Account_1.AccountId = Contact_1.AccountId INNER JOIN
ActivityPointer AS ActivityPointer_1 ON Contact_1.ContactId = ActivityPointer_1.RegardingObjectId
gives the error mentioned above. Each select statement, when run separately, works fine. Any clues?
Thank you in advance
View 4 Replies
View Related
May 17, 2015
I have a table that contains file paths as
ServernamefolderAfilenameA
ServernameFolderBFilenameB
and I need a query to return
ServernamefolderA
ServernameFolderB
I tried
SELECT DISTINCT left(Source, charindex('', Source)- 0) AS String
FROM Table
But that removes everything after the first and I need it to return all data before the last
View 5 Replies
View Related
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
Nov 2, 2006
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?
Can anyone please tell me what's wrong?
View 6 Replies
View Related
Feb 18, 2005
Is there a function in SQL that will find the first character of a character string?
Select Org from PR where id = '100'
Normally, Org will retuen a string such as '1:00'. But, all I want from this string is the first character, '1'.
Thank You
View 3 Replies
View Related
Apr 12, 2007
Hi all,
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?
Thanks,
Jul
View 3 Replies
View Related
Jun 4, 2004
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?
View 1 Replies
View Related
Jun 19, 2006
How can find out the repeated character in each row value of spacific column
View 1 Replies
View Related
May 21, 2008
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
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
Procedure:
Code SnippetCREATE 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?
Any insight would be appreciated!!
View 9 Replies
View Related
Aug 17, 2012
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.
View 2 Replies
View Related
Oct 13, 2015
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.)
Example of data:
StudentID FunctionName Variables
1 Example1 Var1=10&Var2=xy&Sign=True&Role=False
View 2 Replies
View Related
Dec 23, 2014
I have a column in SQL showing Patient Sex (Female/Male)
How Can I update the Column so that I can see only the first character of the string Female and Male( F and M)?
View 2 Replies
View Related
Mar 19, 2007
create a function to reverese a string pass it abcd and it returns dcba
you can't use the built in reverese function...
View 10 Replies
View Related
Oct 11, 2005
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
View 3 Replies
View Related
Aug 22, 2007
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
View 1 Replies
View Related
Nov 20, 2007
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).
Any suggestions?
Thank you for your help!
cdun2
View 3 Replies
View Related
May 21, 2008
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?
Any insight would be appreciated!!
View 15 Replies
View Related
Aug 7, 2006
when i execute in sql this works fine:
xp_cmdshell 'dtexec /f "D:SSISProjectIntegration Services Project1ArchiveMainMultiTables.dtsx" /Conn TahoeDB;"Provider=SQLNCLI.1;Data Source=SE413695AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;"'
but when i execute in C# i get this value
string connect = @"TahoeDB;""Data Source=SE413695AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;""";
xp_cmdshell 'dtexec /f "D:\SSISProject\Integration Services Project1\ArchiveMainMultiTables.dtsx" /Conn "TahoeDB;"Data Source=SE413695\AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;"" ' -- this thorughs up error in sql
Option "Source=SE413695\AASQL2005;Initial" is not valid.This is basically after Data there is a space till Initial and then space catalog.
what should i do ? any help
thanks,
Jas
View 3 Replies
View Related
Aug 27, 2007
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" >
<InsertParameters>
<asp:ControlParameter ControlID="TextBox1" DefaultValue="TextBox1.Text" Name="Surname"
PropertyName="Text" Size="50" Type=Char />
<asp:ControlParameter ControlID="TextBox2" DefaultValue="TextBox2.Text" Name="Names"
PropertyName="Text" Size="50" Type=Char />
<asp:Parameter DefaultValue="TextBox3.Text" Name="Regno" />
</InsertParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1"
runat="server" AutoGenerateColumns="False" AutoGenerateDeleteButton="True" DataKeyNames="ID"AutoGenerateEditButton="True" AllowSorting="True" BackColor="LightGoldenrodYellow"
BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None" PageSize="20"
Height="374px" EmptyDataText="null" DataSourceID=SqlDataSource1 Visible="False" >
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True"
SortExpression="ID" />
<asp:BoundField DataField="Surname" HeaderText="Surname" SortExpression="Surname" />
<asp:BoundField DataField="Names" HeaderText="Names" SortExpression="Names" />
<asp:BoundField DataField="Registration" HeaderText="Registration" SortExpression="Registration" />
<asp:BoundField DataField="Grade" HeaderText="Grade" SortExpression="Grade" />
</Columns>
</asp:GridView>
and the code behind is:protected void Page_Load(object sender, EventArgs e)
{SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=engineering; Integrated Security=True");
conn.Open();
GridView1.DataBind();
conn.Close();
}
public void login1_Click(object sender, EventArgs e)
{
SqlDataSource1.Insert();
Response.Write("you have successfully being added to the database");
can anyone help?!!!
View 1 Replies
View Related
Jun 28, 2004
Hi there,
I have the following code:
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)
Source File: K:detailsdetailspropertylisting.aspx Line: 32
Any ideas how to resolve this?? I do not want to use Parameters in my statement.
Thanks in advance,
TCM
View 3 Replies
View Related
Aug 25, 2000
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.
Thanks
View 2 Replies
View Related
Sep 18, 2001
How to locate last occurrence of character in string?
View 1 Replies
View Related
Dec 2, 2004
Can anyone tell me what this error message means and how I can correct it on my statement:
Code:
Server: Msg 241, Level 16, State 1, Line 1Syntax error converting datetime from character string.
Here's my query statement:
Code:
SELECT animalid AS "Animal ID", name AS "Name", categoryid AS "Category ID", DATENAME(MONTH, dateBorn) + ' ' + DATENAME(DAY, dateBorn) + ', ' + DATENAME(YEAR, dateBorn) AS "Date of Birth"FROM animalWHERE categoryid = 'Cat' AND dateBorn = 'May%'
I know it has something to do with: dateBorn = 'May%' because when I took out Quote: AND dateBorn = 'May%' I get results, but I need to narrow it down to list the cats born in May.
View 6 Replies
View Related
Jan 27, 2006
I'm looking for a string function (or any other quick way) for adding a leading zero to make a string two characters long. For example, if the input is '12' the result will be '12' but if the input is '1', the result will be '01'.
It's actually about making a month number two characters long but I've understood there is no way to make Datediff() to fix it for me.
I thought there might be a string function for this, like there is in many programming languages, but I can't find anything in BOL. I want to keep it simple, because it will be used in a Select and a Group By for aggregating data based on time intervals (year + month).
And, I assume I can't make Datepart() to return year + month directly, only one of the parts at a time.
View 14 Replies
View Related
Jul 10, 2007
Hi,
could you help me. I'd like to get last position of the specified expression in a character string(a fast solution for DB)
Thanks for youe help
View 5 Replies
View Related
Jun 25, 2002
I have a problem finding the last occurrence of a character in a string.
The charindex seems just able to search from left to right..
View 4 Replies
View Related
May 5, 2008
Hi All,
How to find if , if more than 1 on string?
Like
Address
...........
Maidenhead, Berkshire, No SL6-3QH
Queretaro, 76802
22451-041 Rio de Janeiro, RJ,
Neyork city, 900789
Xing City,Petthity,Thaui90
..................................
I need only
....................
Address
...........
Maidenhead, Berkshire, No SL6-3QH
22451-041 Rio de Janeiro, RJ,
Xing City,Petthity,Thaui90
View 1 Replies
View Related
Aug 14, 2007
I am trying to select all members from a SQL db who have a renewal date btw to dates inputted into two text fields, but I am now getting the error above. This is where the problem is coming in...
If strStartDate <> "" and strEndDate <> "" Then
If bParam = True Then
StrSQL = strSQL & " AND "
Else
StrSQL = strSQL & " WHERE deleted=0 AND "
End If
strSQL = strSQL & "renewal_due BETWEEN '%" & Replace(strStartDate,"'","''") & "%' AND '%" & Replace(strEndDate,"'","''") & "%' "
End If
Any help would be greatly appreciated!
Thank you...
View 7 Replies
View Related
Jul 23, 2005
I'm trying to figure out how to find the last whitespace character in avarchar string. To complicate things, it's not just spaces that I'mlooking for, but certain ascii characters (otherwise, obviously, justuse LEN). My initial thought was to REVERSE it, find the location(using CHARINDEX) looking for each of those characters (so, multiplequeries), then subtract that from the LEN of the string.The problem I'm running into is that there are about a dozen differentcharacters we're looking for. Any suggestions? My thought was to(this sounds silly, so there's gotta be a better way) dump the resultsfrom each CHARINDEX into a table, then find the MAX of the table anduse that. But, like I said, it sounds silly. I don't think I can do a[^0-9A-Z] either, since there are non-Alphanumeric characters we'relooking for.Many thanks.
View 10 Replies
View Related
Aug 6, 2007
Hello all,
Im looking for an efficient way to remove all end of line character from a string.
Is there a function to do that or to replace them with another character?
View 1 Replies
View Related
