Hi all,
@str varchar(500)
select @str = 'abcd,efgh,i,jklmn,opsqrtuv'
For the above string, which string function can be used to find the number of occurences of a particular character, for example, ','? For this example, the answer should be 4. Is there any built-in function in SQL to do this?
Thanks in advance,
-Praveena
In my staging table I am having data like below
ABL¯ABL¯0¯0¯ABL¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ABQ¯480¯825¯DLS¯AMA¯ABQ¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ACD¯808¯1255¯DLS¯ELP¯TCS¯PHX¯ACD¯ ¯ ¯ ¯ ¯
ABL¯ADE¯1256¯471¯DLS¯AMA¯ABQ¯LSV¯ADE¯ ¯ ¯ ¯ ¯
ABL¯AFT¯1140¯1744¯DLS¯LAX¯FON¯AFT¯ ¯ ¯ ¯ ¯ ¯
ABL¯AHM¯1178¯1637¯DLS¯LAX¯AHM¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ALB¯1769¯1825¯DLS¯WIL¯ALB¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ALE¯1041¯1150¯DLS¯ALE¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯
Now I want to find the Number of times a '¯'character appears in a string. I should get output 14
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
How can I get a count of occurences of a specific character in a string?
View 6 Replies View RelatedHi,
I'm hitting my head against the wall because of a really small issue...
Basically, let's assume I have the following:
BOOK_ID
========
1
2
2
in a table BOOK. I am trying to code a query that would return the book_id that appears the most in the book table.
Anyone knows how to do this?
The closest i've got is this:
select max(count(book_id)) from intermeeting group by book_id;
But this only returns the number of occurences of the record. What i'm interested in, is the ID of the record that occurs that many times...
Thanks for your help
olo
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
I'm new to SQL and I've been trying this for a while now.
I have let's say a loop of numbers from one to ten. It appears like this:
1
2
3
4
5
What I want to do is to have it appear on one column like this: 12345, the problem is I can't seem to figure out if I need to only have one variable or I'm missing something else, I figured you need to convert them into a string character but I'm still unable to do it.
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?
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] ....
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?
Hi all,
I have a string like '10010000001000'.
Here I need to find the 4th character of this string (in this case it should be '1') by using sql query. How to do this one?
regards
Shaji
actually 123 will come as one twenty three in words & what functionality using in sqlserver2008
View 4 Replies View RelatedHi!
How one can find out the character set/code page set during SQLServer installation?
Thanks,
Fabio
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"
I have following query which return me SP/Views and Functions script using:
select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like
Create proc
create procedure
create proc
create view
create function
I need its result as:
Alter Procedure
Alter Procedure
Alter Procedure
Alter View
Alter Function
I used following
select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?
Hi,
How do I do a conditional check for the left most character if it is an alpha or number in a Derived Column Transformation? Something like
ISNUMERIC(newseq) ? "ABC" : "DEF"
Thanks
I am creating a view to pull data for a UPS integration I am doing. I currently have this view where I pull my data from. All is well accept for my PATINDEX,
Currently I have this PATINDEX So when a user puts this into delivery instructions "#999999" UPS_FINAL returns 999999 which is good for me to use. But The PATINDEX will also grab all kinds of neat stuff out of the delivery instructions field when I really only want whatever is after the # sign in the field.
I am new to sql and dont quite understand how this search is working but I think I am in need of a better way to search the field
Code:
WHEN SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions, PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) IS NULL
THEN dbo.Address_Table.ups_code
ELSE SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) END AS UPS_FINAL
-------------------------------------------------------------------------------------
What I am looking to accomplish is a SUBSTRING that will search delivery_instructions
Code:
SELECT
TOP (100) PERCENT P21PLAY.dbo.p21_view_oe_pick_ticket.pick_ticket_no,
P21PLAY.dbo.p21_view_oe_hdr.order_no, P21PLAY.dbo.p21_view_oe_hdr.customer_id,
P21PLAY.dbo.p21_view_oe_hdr.ship2_name, P21PLAY.dbo.p21_view_oe_hdr.ship2_add1,
P21PLAY.dbo.p21_view_oe_hdr.ship2_add2,
P21PLAY.dbo.p21_view_oe_hdr.ship2_city,
P21PLAY.dbo.p21_view_oe_hdr.ship2_state,
P21PLAY.dbo.p21_view_oe_hdr.ship2_zip,
P21PLAY.dbo.p21_view_oe_hdr.po_no,
P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id AS Carrier,
P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id AS Supplier,
P21PLAY.dbo.p21_view_oe_hdr.cod_flag,
P21PLAY.dbo.p21_view_oe_hdr.terms,
P21PLAY.dbo.p21_view_oe_hdr.ship2_country,
P21PLAY.dbo.p21_view_oe_hdr.ship_to_phone,
P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
dbo.Address_Table.ups_code,
-----------Looks for special character and returns next 6 spaces as UPS_Shipper----------
SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions, PATINDEX('%[^a-z ]%',
P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6)
AS UPS_Shipper,
------------------Checks view for email address or assigns alternate------------------
(CASE WHEN charindex('@', p21_view_contacts.email_address) > 0 THEN p21_view_contacts.email_address ELSE
'email@domain.com' END) AS alternate_address,
'Y' AS QVN, 'email@domain.com' AS failureaddress,
P21PLAY.dbo.p21_view_contacts.email_address,
------------When carrier_id is not one of these # then Null; else ------------------------------
CASE WHEN P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id NOT IN (105188, 105191, 105194, 105197, 105200,
105203, 105206, 105209, 105212) THEN NULL
----------------- Looks for special Character in delivery_instructions; if NULL then ups_code;
ELSE return value from delivery_instructions as UPS_Final--------------------
WHEN SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) IS NULL
THEN dbo.Address_Table.ups_code
ELSE SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) END AS UPS_FINAL
FROM dbo.Address_Table INNER JOIN
P21PLAY.dbo.p21_view_oe_pick_ticket INNER JOIN
P21PLAY.dbo.p21_view_oe_hdr ON P21PLAY.dbo.p21_view_oe_pick_ticket.order_no =
P21PLAY.dbo.p21_view_oe_hdr.order_no ON
dbo.Address_Table.id = P21PLAY.dbo.p21_view_oe_hdr.customer_id LEFT OUTER JOIN
P21PLAY.dbo.p21_view_contacts ON P21PLAY.dbo.p21_view_oe_hdr.contact_id = P21PLAY.dbo.p21_view_contacts.id
WHERE
(P21PLAY.dbo.p21_view_oe_hdr.completed <> 'Y')
AND (P21PLAY.dbo.p21_view_oe_hdr.delete_flag <> 'Y')
AND (P21PLAY.dbo.p21_view_oe_hdr.will_call <> 'Y')
ORDER BY P21PLAY.dbo.p21_view_oe_pick_ticket.pick_ticket_no
Hope this makes since
Hi I have a text file which I need to import into Access or SQL.
It is 500,000 records which is pipe delimited. The problem is, is that it can contain carriage returns: (square symbol).
Therefore I need to find and replace these characters
Does anybody know of any free ware text file viewers that can do this??
Best Regareds
David
Hello:
I have a field hold varchar. I would like to calculate how many character in the field when user enter the data and save in the database.
Is there any way to do?
Thanks,
Snow
I have a field that contains number but it actually is character. I want to convert it to number so i can put it into query like this:
sum(field) as Totalmyfield.
I have a text field which has entries of variable length of the form:
"house:app.apx&resultid=1234,clientip"
or
"tost:app.apx&resultid=123,clientip"
or
"airplane:app.apx&resultid=123489,clientip"
I'm trying to pick out the numbers between resultid='...',clientip no matter what the rest of the string looks like. So in this example it would be the numbers:
1234
123
12389
the part of the string of the form resultid='...',clientip always stays the same except the length of the number can vary.
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
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
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?
How can find out the repeated character in each row value of spacific column
View 1 Replies View RelatedI'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!!
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 RelatedI 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
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)?
create a function to reverese a string pass it abcd and it returns dcba
you can't use the built in reverese function...
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 RelatedI 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).
Any suggestions?
Thank you for your help!
cdun2