How To Count # Of Occurrences Of A Character Inside A String?

Jan 24, 2008

I have a string of characters in my data flow and I need to add a derived column showing the # of times a certain character appears within that string. For example, my string in the data flow is:

NNNNNRJKSURNNNEJNNNN

Now I need to count the number of "N"s in that column. From the example above, I should get the integer 12, and that would be the value of my derived column. Any ideas?

View 7 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Replace Multiple Occurrences Of Same Character With Single Character

Aug 6, 2015

I have the following scenario, The contents of main file are like :

ServerCentral|||||forum|||||||||||||||is||||||the||best
so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it

And I need the output in the following form:

ServerCentral=forum=is=the=best
so=be=on=it

The logic being that multiple and consecutive occurrences of the special character, here - pipe , should be replaced by a single special character.

View 5 Replies View Related

Find The Occurrence Of A Particular Character Inside A String

Dec 5, 2007

Hi all,

Am searching for a built-in function that retrives me the occurrence (counts) of a particular character inside a string.


For e.g. In the String 'SqlServer'. I want to find the no of the character 'e', which shud retrive the value as 2.

Can this be acheived through any function.

Cheers !
ash

View 6 Replies View Related

Record Count - Conversion Failed When Converting Date And / Or Time From Character String

Sep 26, 2014

I am trying to write a stored procedure that loops through the list of user tables, gets the record count for each one and write a record to an audit table with DATE, TABLENAME, RECORDCOUNT.I keep getting an error "Conversion failed when converting date and/or time from character string".Here is the script...

DECLARE @table nvarchar(500)
DECLARE @sql nvarchar(520)
DECLARE CursorSelect CURSOR FOR
select table_name from INFORMATION_SCHEMA.tables where table_name not like 'sys%' order by table_name

[code]....

View 2 Replies View Related

COUNT Occurrences Of Value In Aggregate Query

Jun 1, 2008

I have a table as below:
COMPUTERNAME, COUNTER, REASON
WXP-13Failed
WXP-1113Failed
WXP-38Failed
WXP-910Failed
WXP-117Success

What I would like to do is create a percentage of Success vs. the Failed for the same Computername.
Problem is when I use Group by I cannot tell the COUNTER of the Success anymore. This is what I have been using:

SELECT COMPUTERNAME, SUM(COUNTER) AS COUNTERTOTAL
FROM
ReturnTable
GROUP BY COMPUTERNAME

Thank you. The above query actually targets a result table from another query, but that should not matter.

View 11 Replies View Related

SQL Server 2012 :: Retrieve String Between Two Delimiters For Multiple Occurrences?

Oct 21, 2015

How can we get the result set as TESTING1,TESTING2 from below

DECLARE @MyString varchar(256) = '$I10~TESTING1$XYZ$I10~TESTING2$~'
Basically i need to get all the substrings which are in between $I10~ and $

View 6 Replies View Related

T-SQL (SS2K8) :: Find String Before Character When Character Appears Multiple Times

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

Select Part Of Character String Based On A Character

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

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

To Find The Special Character Inside A Name

Mar 21, 2006

CHALASANI writes "I have a name in my table which is "ra_ja" I want to find the name with the help of the'_' character.

Can you please help with the querry

Sorry with my english
Thank you"

View 4 Replies View Related

Replace A Character Inside A Field

Mar 24, 2008

Hi, I need to replace a character from a column of a txt file. I have defined the column and the values (datetime type for sql server) that I receive are like this: 2008-02-15-20.07.19
So, I need to replace the "." with a ":" beacuse those are minutes and sql server uses ":" for minutes
How can I do this? Any help?
Thanks

View 9 Replies View Related

Character Limit For Variables Inside A Stocked Procedure

Feb 23, 2007

I am currently having a problem where my SQL server seems to lock any variables to 1000 characters (ie. varchar(8000) can only hold 1000)I have read in numerous sources it was possible to change that limit so the varchar can truly hold the 8000 characters and not stop at 1000, but there was no info on how to do this.I am looking for a "How to" to put this limit to 8000.Thank you! 

View 10 Replies View Related

String Or Binary Data Would Be Truncated. (only For 1700 Character String?)

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

Help !!! How Do I Count Character Results!

Apr 13, 2007

I have the following problem, I need to create the TotalsY and TotalsN values.

Can anybody give some direction on how to count these character values.



Column1 Y

Column 2 Y

Column 3 N

TotalsY 2

Totals N 1



Column1 =First(Fields!NOB_Pickup_L_D_T.Value)

Column 2 =First(Fields!NOB_Return_L_D_T.Value)

Column 3 =First(Fields!NOB_New_L_D_T.Value)

TotalsY 2 <I Dont Know>

Totals N 1 <I Don't Know>





Thanks,

Rick

View 1 Replies View Related

Usage Of Count() Function Inside Sql Transaction

May 9, 2007

Please find my second post in this thread.

Can anyone help me in sorting out the problem and let me know what might be the reason.

Thanks & Regards

Pradeep M V

View 19 Replies View Related

How To Count The Tasks Inside A Package Programmatically?

May 7, 2007

Hi there,

I'm trying to keep track of the ETL process inserting/updating a row in one table for each package that finish in my ETL process when executing. So far, I created a Script task that increments by one a variable (counter) and then open a connection to my database an insert/update my table. What I want to see is Step 1/30, Step 2/30 and so on. Right know I can display Step 1, Step 2 but how can I get the overall number of tasks within a package?


Thanks in advance for any help you can give me.

Sincerely,
Ricardo

View 10 Replies View Related

SQL Server 2012 :: Character Value Count From Dataset

Oct 31, 2014

Consider following script

create table data_set (id int primary key, col1 varchar(10))
go
insert into data_set values (1,'a'), (2,'b'),(3,'c'),(4,'d'),(5,'a'),(6,'b'),(7,'e'),(8,'f'),(9,'a'),(10,'a')
select * from data_set

I tried this below

Declare @child_ids int
@col_val varchar
@count int

select @child_ids, @col_val, @count, count(col1) as records from data_set
group by col1
order by col1

But I want my output like below .....

View 2 Replies View Related

Problem About Caculating Wide-char Strings' Character Count

Jan 8, 2008



it's like this, i have a temporary table such as
create table temp_table (str varchar(50))
and i have a data table
create table data_table (str varchar(20))

now i import my data(in which there is some corrupted lines) into the temporary table, they should be all ansi-character strings and no more than 20 characters, but now some wrong datas in which there are wide-characters are mixed in. as the result of these wide-characters, the corrupted strings each takes over 20 bytes, but i can't filter them out, as when i enter in "len(str)", the sql server returns character counts, instead of byte counts, i thought this should only happen when i was using a unicode date type!(e.g. nvarchar). but now the server also behaves like this on those ansi date types. it seems all string manipulating functions refering string length behaves like this.

so when i am trying to run:
insert into data_table select str from temp_table where len(str) <= 20
or
insert into data_table select left(str,20) from temp_table

it will always end up with a string truncating error
String or binary data would be truncated. The statement has been terminated

So now my problem is how to get the count of byte, but character, of a string containing wide-characters?


i'm using sql server 2005 standard version with sp2

View 2 Replies View Related

SQL Get First Character Of A String

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

String With 15,500 Character

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

Uniqueidentifier Param Inside A SQL String

Apr 7, 2006

I have a uniqueidentifier probleme when i want to execute my sql string. The "PortalID" is my uniqueidentifier passed on my StoredProc parameter. But i cant make it work. It said that i cant add a nvarchar with a uniqueidentifier.!!
what is the best way to do it!!  i try to convert(nvarchar(40), @PortalID) but its not workingdeclare @sql nvarchar(4000)
set @sql = 'select
t.[PortalID],
t.[City],
t.[Name],
t.[Code],
ts.*
from [Hockey_Team_Statistics] ts inner join [Hockey_Teams] t on (t.[TeamID] = ts.[TeamID])
where ts.[SeasonYear] = '+CONVERT(nvarchar(10),@SeasonYear)+' and ts.[LeagueMode] = '+CONVERT(nvarchar(1),@LeagueMode)
+'t.[PortalID] = '+@PortalID+'

order by '+ @SortExpression +''

exec sp_executesql @sql 

View 3 Replies View Related

T-SQL (SS2K8) :: Wrap Varchar Field Based On Character Count And Spaces

Dec 8, 2014

Because of a limitation on a piece of software I'm using I need to take a large varchar field and force a carriage return/linebreak in the returned sql. Allowing for a line size of approximately 50 characters, I thought the approach would be to first find the 'spaces' in the data, so as to not split the line on a real word. achieve.

--===== Simulate a passed parameter
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = (select a_notes
from dbo.notestuff as notes
where a_id = '1')

[Code] .....

View 5 Replies View Related

Reading Each Character In A String

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

Repeated Character In String

Jun 19, 2006

How can find out the repeated character in each row value of spacific column

View 1 Replies View Related

Parsing Character String

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

Adding Character In Between A String

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

Separate String For Each Row By & Character

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

Update And Get First Character Of String

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

Reversing A String Character

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

Changing The 6th Character Of A String?

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

Splitting A String By The '|' Character

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

Strange Character At End Of String

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

Parsing Character String

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







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