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?
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
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.
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
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!
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'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?
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
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
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 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+'
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')
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?