I am trying to replace all special characters in a column with one special character.
Example:
Table: dbo.Employee Column: Name Name ------- edwardneuman!"<] mikemoreno)'>$: JeffJensen"?>"
I am trying to get the namepart to the left of ANY special character. To achieve this, I am thinking of replacing all the special characters with a single special character so that I can find the first occurrence of that special character and grab left of the special character (SUBSTRING/CHARINDEX). This way I don't need to loop through all the special characters.
I am expecting the following results:
Name ------- edwardneuman<<<< mikemoreno<<<<< JeffJensen<<<<
I have a problem where I want to write a function to remove recurring characters from a string and replace them with a single same character.
For instance I have the string '12333345566689' and the result should be '12345689'. In Oracle I could do this with "regexp_replace('12333345566689', '(.)1+', '1')", but in T-SQL the only solution I could think of is something like this:
DECLARE @code NVARCHAR(255) SET @code = '12333345566689'; SET @code = REPLACE(REPLACE(REPLACE(@Code, '1', '~1'), '1~', ''), '~1', '1');
and repeat this for 2 - 9. But I'm sure there is a more elegant version for this in SQL Server 2012.
I'm retreiving data from a SAP BW data source. In the dataset designer I can build a query that returns a single value, the data returns successfully in the designer. When I switch to the layout mode I can place the field of the datset in table / matrix / textbox. However when i switch to preview mode or deploy the report and try to see them in Report Manager the report fails to render with the following message:
Query Execution Failed for dataset 'XXX'
These columns don't currently have unique values.
Its a single cell datset?!?!
I can successfully retreive data from other SAP Infocubes and queries. I also cannot replicate the error using a SQL Server datasource. The stack trace error looks like this:
w3wp!processing!6!5/9/2007-16:57:41:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'DataSet1'., ; Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'DataSet1'. ---> System.ArgumentException: These columns don't currently have unique values. at System.Data.ConstraintCollection.AddUniqueConstraint(UniqueConstraint constraint) at System.Data.ConstraintCollection.Add(Constraint constraint, Boolean addUniqueWhenAddingForeign) at System.Data.DataTable.set_PrimaryKey(DataColumn[] value) at Microsoft.ReportingServices.XmlaClient.CubeMetadataCache.CreatePrimaryKeys(DataTable table, InternalObjectType objectType) at Microsoft.ReportingServices.XmlaClient.CubeMetadataCache.Populate(InternalObjectType objectType) at Microsoft.ReportingServices.XmlaClient.CubeMetadataCache.Microsoft.ReportingServices.XmlaClient.IMetadataCache.Populate(InternalObjectType objectType) at Microsoft.ReportingServices.XmlaClient.ObjectMetadataCache.Microsoft.ReportingServices.XmlaClient.IObjectCache.Populate() at Microsoft.ReportingServices.XmlaClient.CacheBasedFilteredCollection.PopulateCollection() at Microsoft.ReportingServices.XmlaClient.DimensionPropertyCollectionInternal.Find(String index) at Microsoft.ReportingServices.XmlaClient.DimensionPropertyCollection.Find(String index) at Microsoft.ReportingServices.DataExtensions.SapBw.DataReader.FindOptionalPropertyName(String internalColumnName, CubeDef cube, Boolean fIsYukonSP1) at Microsoft.ReportingServices.DataExtensions.SapBw.DataReader.GenerateColumnInformation(CubeDef cube, Boolean fIsYukonSP1) at Microsoft.ReportingServices.DataExtensions.SapBw.DataReader..ctor(AdomdDataReader dataReader, CubeDef cube) at Microsoft.ReportingServices.DataExtensions.SapBw.SapBwCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSetNode.RunDataSetQuery()
Any ideas why this happening and how I can get around it?
Thanks
Sanjay
ps Why would you want to put a unique constraint on a read only single table dataset?
I need to write a select query that gets the values of all columns but only returns unique sites because some sites are load balanced across several servers and where this is the case I don't want the site to appear multiple times in the list.
Our clients want to be able to do full text search with a single letter. (Is the name Newton, Nathan, Nick?, Is the ID N1, N2...). Doing a single character full text search on a table work 25 out of 26 times. The letter that doesn't work is 'n'. the WHERE clause CONTAINS(full_text_field, ' "n*" ') returns all rows, even rows that have no 'n' in them anywhere. Adding a second letter after the "n" works as expected.
Here is an example
create table TestFullTextSearch ( Id int not null, AllText nvarchar(400) ) create unique index test_tfts on TestFullTextSearch(Id); create fulltext catalog ftcat_tfts;
begin SET @STR='INSERT INTO tbl_Level1' + '(' + @columnName + ',orgID' + ')' + 'Values' + '(' + @Level1Name + ',' + @orgID + ')'
EXEC @STR
ERROR :- Conversion failed when converting the varchar value 'INSERT INTO tbl_Level1 (Level1Name_HIN,orgID ) Values (@Level1Name,' to data type int. pls help me out
item custclass totalcustclass ------------------------- 06-5841 INST-CLINPRAC 1 06-5841 INST-MKT/MEDIA 2 06-5841 PROGRAM 1 06-5841 STANDARD 4 06-5845 STANDARD 1 AX-048 INST-MKT/MEDIA 4 KT-048 PROGRAM 2 KT-048 STANDARD 4
i want condition like if item is starting with number then totalcustclass whcih is count(*) remain same giving correct results...but if item startign with ascii character then totalcustclass r getting double so i have to /2..
i want results:
item custclass totalcustclass ------------------------- 06-5841 INST-CLINPRAC 1 06-5841 INST-MKT/MEDIA 2 06-5841 PROGRAM 1 06-5841 STANDARD 4 06-5845 STANDARD 1 AX-048 INST-MKT/MEDIA 2 KT-048 PROGRAM 1 KT-048 STANDARD 2
select item, custclass,
case when item is <ascii> then count(custclass)/2 else count(custclass) as totalcustclass
from itemcustclass
can anyone tell me what condition will come in case?
i have a field with 2 characters(many of them ) and spaced inbetween them which are codes for something
ie XA OX YY BY CY DY XC XD OE In any or all combination
i want to make a count of rows which contains BOTH XA BUT DOES NOT CONTAIN any one or all of XC,XD & OE
SO I write the query select count(colname) from tablename where colname LIKE ('%XA%') AND colname NOT LIKE ('%XC%') OR colname NOT LIKE ('%XD%') OR colname NOT LIKE ('%OE%') ')
iS this correct why then it does not exclude the XC,XD,OE
I have a field in my table named x_CFSNUM that is filled with various 4 digit numbers- ex: 0067, 0068, 0097, etc. These are not unique.
I would like to write a query that will tell me the sum of the times the highest number occurs.
For Example: If these were the numbers: 0067, 0067, 0067, 0089, 0094, 0095- The query would produce the quantity of "3" because 0067 is listed 3 times.
I know this is confusing. Please let me know if you need any clarification.
I am getting a tab character at the end of my query fields, been trying various things to fix, such as using the replace function below but still i get the tabs!
select CAST(REPLACE(NAMEALIAS,CHAR(9),'')AS CHAR(40)) + ',' as PRODNAME, CAST(REPLACE(ISNULL(GLOBALTRADEITEMNUMBER,0),CHAR(9),'')AS CHAR(18))as EANNO,LTRIM(cast(ISNULL(GLOBALTRADEITEMNUMBER,0) as char(18))) as KONSEAN ,LTRIM(CAST(I.ITEMID AS CHAR(8))) AS PRODCODE,'00' from INVENTTABLE I LEFT JOIN INVENTITEMGROUPITEM IG ON I.ITEMID = IG.ITEMID
i am using SQL server for turkish language and i have a problem with turkish character "i". Whenever "i" comes in any word of query it gives wrong result. It also not able to recognize the small and capital "i" of turkish.
I have a problem with a SQL SELECT query. As far as my research goes i figured out that UNIQUE is used when you have one column that you whant unique and DISTINCT is used when you have more than one column that you want to all be unique. But i have a query where i want just some of the columns to be unique and some not. Here is the full query (It's in a stored procedure):
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[rpt_ExecSum_Combined_4fields](@BondGroupID int) AS SELECT DISTINCT SUM(dbo.Bond.LoanAmount) AS SumOfBondValue, COUNT(dbo.Bond.BondID) AS CountOfBond, dbo.ProgressStepType.ProgressStepType FROM dbo.BondGroup INNER JOIN dbo.TransactionTable ON dbo.BondGroup.BondGroupID = dbo.TransactionTable.BondGroupID INNER JOIN dbo.Bond ON dbo.TransactionTable.TransactionID = dbo.Bond.TransactionID INNER JOIN dbo.ProgressStep ON dbo.TransactionTable.TransactionID = dbo.ProgressStep.TransactionID INNER JOIN dbo.ProgressStepType ON dbo.ProgressStep.ProgressStepTypeID = dbo.ProgressStepType.ProgressStepTypeID WHERE (dbo.BondGroup.BondGroupID = @BondGroupID)
GROUP BY dbo.ProgressStepType.ProgressStepType, dbo.ProgressStep.ProgressStepID HAVING (dbo.ProgressStepType.ProgressStepType = 'AIP (Approval in Principle) received') OR (dbo.ProgressStepType.ProgressStepType = 'Grants') OR (dbo.ProgressStepType.ProgressStepType = 'Attorney') OR (dbo.ProgressStepType.ProgressStepType = 'Feedback received from bank') ORDER BY dbo.ProgressStepType.ProgressStepType
********* With the DISTINCT it gives me a full set of unique values but what i want is this: Every CountOfBond may have more than one SumOfBondValue and they may be the same, but if there is more than one dbo.ProgressStepType.ProgressStepType the query should only read it once. In other words the dbo.ProgressStepType.ProgressStepType is a unique value and the rest not...
Hi, I have been asked to write some code that can check a large table for duplicate values in a non pk column. The table may have up to 1000000 rows. The PK column is an auto increment field. For performance reasons the column in question could not be set to unique values only for inserts, an algorithm is used to create unique no's before the insert but what I am doing is double checking that their have been no duplicates created accidently. If their are duplicates I need to know what rows they occurred on.
I'm working in a ASP.NET 2.0 application with a SQL Server 2000 database on the back end. I have a strongly typed dataset in the application that calls a stored procedure for the select. I'm having trouble filling the dataset at runtime though. I am trying to use a character string query because I setup different columns to be pulled from a table each time and in a different order so my T-SQL looks like this: set @FullQuery = 'Select ' + @FieldsinOrder + ' from tblExample'exec (@FullQuery) This works fine in query analyzer. The results return and display correctly. However, when I run the application, the dataset does not get filled. It is like the results do not output to the application. If I change the query to be a normal select it works. For example: select * from tblEmample That works fine. What is it about a select query setup as a character string and then executed that ASP.NET doesn't like?
I have an issue with some data that has a leading ASCII char 160 (the "a" with the accent mark) but it shows in query results as a space.
... where customername like char(160) + '%'
returns 2 rows but shows the customer name with a leading space. How would I change the collation or do otherwise to get this character to display correctly in the results?
How would I get the unique email addresses and its associated row of data from a SQL Server table that has no unique fields defined? If there is a duplicate email address then only show the first one and not the other rows with the same email address. Example table and data UserID LastName Email997249 MCCO-49 S.MCCO-49@SampleISD.org997462 BATE-62 A.BATE-62@SampleISD.org997605 DENS-05 B.DENS-05@SampleISD.org 997622 KAIS-22 A.KAIS-22@SampleISD.org997623 KAIS-22 A.KAIS-22@SampleISD.org997624 KAIS-22 A.KAIS-22@SampleISD.org997625 KAIS-22 A.ZKAIS-22@SampleISD.org997626 KAIS-22 AX.ZKAIS-22@SampleISD.org997627 KAIS-22 AX.KAIS-22@SampleISD.org Result UserID LastName Email997249 MCCO-49 S.MCCO-49@SampleISD.org997462 BATE-62 A.BATE-62@SampleISD.org997605 DENS-05 B.DENS-05@SampleISD.org 997622 KAIS-22 A.KAIS-22@SampleISD.org997625 KAIS-22 A.ZKAIS-22@SampleISD.org997626 KAIS-22 AX.KAIS-22@SampleISD.org Thanks
Hi - I've got a table in SQL server that has its unique ID field as binary data. I have a gridview displaying data from this table and I have set the datakey of this gridview as that binary data field.I have a 'Select' ciolumn in my gridview that, when selected, will display more details from the selected record. The problem is, how do I pass the selected id back to sql server bearing in mind that it's binary data? Here's what I'm doing at the moment:I have a function in my data accsess class that queries the database:Public Function getDetailsById(ByVal Id As System.Byte) As DataSet Dim con As New SqlConnection(conStr) Dim cmd As New SqlCommand("SELECT * FROM tableName WHERE Id=@Id", con) cmd.Parameters.Add("@Id", SqlDbType.Binary) cmd.Parameters("@Id").Value = Id Dim ds As New DataSet Dim adp As New SqlDataAdapter(cmd) adp.Fill(ds) Return ds End FunctionAnd then I call this function from my page with the gridview using: Dim da As New myDataAccess Dim ds As New DataSet ds = da.getDetailsById(GridView1.SelectedDataKey.Value)But I get the error:Conversion from type 'Byte()' to type 'Byte' is not valid.Any ideas where my data/code is going wrong? I'm not sure how to pass this sort of data around?Thanks
I am trying to create a query to return the latest record from a dataset. The code created so far returns multiple records, where I need a single record to be returned.
hilet me explain my need..following are the tables im using..tbl_company (company table - parent) id company 1 test 2 test123 tbl_dept (department table - master) id dept 1 dept1 2 dept2tbl_compdept (company departments table - child) cmpid deptid 1 1 2 1 2 2 wats my need is.. while the company is listing..by query using joins, result was like this..company depttest dept1test123 dept1test123 dept2i need company test123 should be listd only once..when i use group by or distinct means, all r listed..is there any way to filter out therepeating company list by just listing the company list only once..
I am having a bit of difficulty trying to work how to write a query. I am normally only used to doing SELECT, UPDATE and DELETE queries ... the simple ones.
However I have come across a query whereby things get a lot more complex.
My company has an employee table with a way to store data that I have not been used to.
EMPLID, NAME, DATELASTMODIFIED, EmployeeStatus. (There are lots more but N/A)
The problem that I have is that there can be more than one row for an employee id. Therefore I cannot uniquely identify an employee id. Instead what I have discovered I have to use is to find an employee with the last modified date higher than the other rows with the same employee id.
The query I am trying to achieve is:
Code:
Count all employees that have an employee status of 'Employed'.
(Therefore counting only one employee per id rather than counting the same employee more than once)
The trouble is, translating this into a SQL query. I have tried to have a go but don't know how to query:
Code:
SELECT COUNT (DISTINCT EmplId)[ FROM Employeetable AS e WHERE e.LASTDATEMODIFIED <And Stuck> AND EmployeeStatus = 'Employed'
So I thought hmm lets pseudo code this, but then I get stuck into compelixty:
Code:
SELECT * FROM EmployeeTable WHERE EmployeeStatus = 'Employed'
int counter = 0; ForLoop (EmplId) { LastDate Date if(LASTMODIFIEDDATE > LastDate) { LastDate = LASTMODIFIEDDATE } //How do I return the value? Do I count the value? }
Has anyone come across this issue before? Can anyone help it quite fundemental to what I have to query so help on this is highly appreciative.
select * from bigtable where product_code in ('abc','def','hij','klm')
I know that each of those product_codes will return a large data set. What I'd like to do is to run the same query but constrain the result set to a max rownum of 200.