SQL Server 2008 :: Trying To Print Records That Have Only English Characters
Jul 17, 2015
I have a small table that looks like this
create table Test(ID number, Name nvarchar(500));
insert into Test(1,'abc testing');
insert into Test(2,'abc include persian آنا اسمیت');
insert into Test(3,'mnp testing');
insert into Test(4,'abc include Russian Джон Тед');
I want to get records that have only english characters i.e ID=1 and 3 only.
I tried select * from Test where Name like '%[a-zA-Z0-9]%' but this will return all 4 records. How can I accomplish this?
Hi Friends, I am trying to insert Hebrew string into my database from my Java based tool. I am using SQL Server 2005 and the latest MS SQL jdbc driver. After i insert the string, all the Hebrew characters are in an unreadable format (some junk basically). My requirement is to download the rows of that particular table of the database (containing Hebrew) into an EXCEL sheet, give the corresponding english translation and upload it back. But since i am getting junk characters in the excel sheet, i am unable to translate :( I have tried changing the COLLATE parameter of the database and tables while creating the database, but still the issue persists. Please help me. Thanks in advance!
Hi Friends, I am trying to insert Hebrew string into my database from my Java based tool. I am using SQL Server 2005 and the latest MS SQL jdbc driver. After i insert the string, all the Hebrew characters are in an unreadable format (some junk basically). My requirement is to download the rows of that particular table of the database (containing Hebrew) into an EXCEL sheet, give the corresponding english translation and upload it back. But since i am getting junk characters in the excel sheet, i am unable to translate I have tried changing the COLLATE parameter of the database and tables while creating the database, but still the issue persists. Please help me. Thanks in advance!
Hello all,We are developing an add-on for GoldMine, however my problem is ageneric SQL Server problem. The situation is this; we have a database,with collation set to SQL Latin. When we connect to database viaGoldmine, Enterprise Manager or Delphi (through ADO) we cannot seeTurkish characters. When I set SQL Server machine's "Language ForNon-Unicode Programs" setting to Turkish, GoldMine works fine, howeverthere is still problem in EM and Delphi.Does anybody know how to solve this issue?
Hi,We are developing a small web interface to a local ERP software, whichuses SQL Server 2000 as database. The database uses SQL_Latin1_CP1collation, and the fields are varchar (not nvarchar), however, the mainprogram inserts and reads non-English (Turkish) characters into thesecolumns. However, when we connect to database with ADO.NET, thesecharacters are not read correctly. (The situation is same when I checktables with Enterprise Manager and Query Analyzer)In a past situation (which was about a Win32 application), I have heardabout character conversion behaviour of ADO (and many other DBlibraries) and solved that problem using BDE instead of ADO, so thatthe connection is made via DB-Library instead of OLEDB.But this way cannot be applied to my ASP.NET situation, and there is NOway to change database collation. Must I use a ADO.NET property, or useanother provider, or maybe another library? Any advices? Thanks...
Hi Friends, I am trying to insert Hebrew string into my database from my Java based tool. I am using SQL Server 2005 and the latest MS SQL jdbc driver. After i insert the string, all the Hebrew characters are in an unreadable format (some junk basically). My requirement is to download the rows of that particular table of the database (containing Hebrew) into an EXCEL sheet, give the corresponding english translation and upload it back. But since i am getting junk characters in the excel sheet, i am unable to translate I have tried changing the COLLATE parameter of the database and tables while creating the database, but still the issue persists. Please help me. Thanks in advance!
where, statString is a string variable containing Hebrew characters.
Till here, my code works fine. i.e, Hebrew characters are properly inserted to the database. The problem is when I try to retrieve the String_Id based upon the statString I inserted to the table static_string1.
Code Snippet String sql = "select String_Id from Static_String1 where String like ('" + statString +"')"; Statement statement=connection.createStatement(); ResultSet rs=statement.executeQuery(sql); rs.next(); int stringId=rs.getInt("String_Id");
I tried hardcoding the string in the query and to execute it from the SQL Server Management Studio as below
Code Snippetselect String_Id from Static_String1 where String like( ' הזח' );
But even this is returning null rows, even though the entry is present in the table Please help me out asap.
Please pardon me if this is not the right section to post my doubt. I didnt find any other relevant section here.
declare @table table ( ParentID INT, ChildID INT, Value float ) INSERT INTO @table SELECT 1,1,1.2
[code]....
This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....
Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending
I am having an issue fetching Chinese characters in a XML data type. It return questions mark (?).
Below is the sample script.
DECLARE @XMLVAR XML SET @XMLVAR = '<?xml version="1.0"?> <POLICY_SEARCH xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <NAME>QA*保险1</NAME><NUMBER /></POLICY_SEARCH>'
SELECTI.xmlParam.query('./NAME').value('.','NVARCHAR(25)') NAME ,I.xmlParam.query('./NUMBER').value('.','NVARCHAR(25)') NUMBER FROM@XMLVAR.nodes('POLICY_SEARCH') AS I(xmlParam)
I have an issue where I am storing various international characters in nvarchar columns, but need to branch the data at one point of processing so that ASCII characters are run through an additional cleansing process and all non-ASCII characters are set aside.
Is there a way to identify which nvarchar values are within the ASCII range and can be converted to varchar without corruption? Also, the strings may contain a mix of english and international character sets, so the entire string must be checked and not just the first character.
I need to find all uses of special characters in a database. I used the following code to do this:
USE dbName GO IF OBJECT_ID('tempdb.dbo.#Results') IS NOT NULL DROP TABLE #Results GO
[code]...
This will check all tables in the database, but if you want to check specific tables you can uncomment the line in the where clause and specify tables to be checked. The query will return any text fields that have any characters other than letters, numbers or spaces.
This code works fine for me because all the tables in my database have single column primary keys. However I know how much Jeff Moden hates cursors or RBAR queries, so my question is could this have been done by any method other than using a cursor?
Part 1: When there is ~ (tilde) and has any value after it then it goes into a new row and duplicating the other columns like the facility in the screenshot attached and new column having the sequence.
Part 2: When there is ^ (Caret) its a new column irrespective of a value present or not
CREATE TABLE [dbo].[Equipment]( [EQU] [VARCHAR](50) NOT NULL, [Notes] [TEXT] NULL, [Facility] [VARCHAR](50) NULL) INSERT INTO [dbo].[Equipment] ([EQU] ,[Notes] ,[Facility]) SELECT '1001','BET I^BOBBETT,DAN^1.0^REGULAR^22.09^22.090~BET II^^^REGULAR^23.56^0~','USA' union SELECT '998','BET I^JONES, ALANA^0.50^REGULAR^22.09^11.0450~BET II^^^REGULAR^23.56^0~','Canada' UNION select '55','BET I^SLADE,ADAM F.^1.5^REGULAR^27.65^41.475~','USA' SELECT * FROM dbo.Equipment
I created the table in excel and attached the screenshot for a clear picture as to what is required. I use text to Columns in excel to achieve this not sure if there is anything similar in sql.
I'm presented with a problem where I have a database table which must be migrated via a "custom tool", moving the data into a new table which has special character requirements that didn't exist in the source database. My data resides in an SQL Server 2008R2 instance.
I envision a one-time query which will loop through selected records and replace the offending characters with --, however I'm having trouble understanding how this works.
There are roughly 2500 records which meet the criteria of "contains bad characters", frequently containing multiple separate bad chars, and the table contains roughly 100000 rows.
Special Characters are defined as #%&*:<>?/{}|~ and ..
While the field is called "Filename" it isn't always so, it is a parent/child table where foldernames are also stored.
The examples I'm finding are all oriented around SELECT statements, to change the output of what I see returned, however I'd rather just fix the entire column using an UPDATE. Initial testing using REPLACE fails because I don't always have a single character as the bad thing in a string.
In a better solution, I found an example using a User Defined Function to modify the output of a select, but I cannot use that UDF in an UPDATE.
My alternative is to learn enough C# to modify the "migration tool" to do this in-transit, but I know even less about C# than I do of SQL.
I gather I want to use @@ROWCOUNT to loop through the rows but I really can't put it all together in a cohesive way.
I have a table of customer sales (dbo].[CustomerSales] which has a complete record od sales of our products for the past 5 years. I would like to create a report that extracts all the sales per customer on one row ie [CustomerID] 00011 has had 25 sales in the past 5 years and I would like to have their sales from left to right (starting with their earliest sale date at the beginning [SaleDate] field on the one row. I would also like to include the store location field [Location] along with the date the sale took place. So in other words the extract would look something like:
I'm using SQL Server 2008 SP3 with Reporting Services 2008 SP3.
By opening my web application in IE9 and running report, i can print and show then print preview by clicking the "Print preview" button in printing dialog.
By opening my web application in IE11 and running report, i can print the report but if I click on the print preview on the print dialog nothing appears... Why?
My query is pulling to many records becuase of the last join. This table can have multiple registration files for a computer. I just want the latest one or last one insert which is based on dttRegistration. I thought a top 1 select and order by would do it, but now returns no computer names.
tblCustomers contains a CustomerID that is unique to each customer.
tblMachines contains a list of all machines with a MachineID that is unique to each machine. It also contains the CustomerID number.
tblServiceOrders contains a list of each time each customer was serviced. It contains the ServiceDate, CustomerID, and ServiceOrderNo. But it does not have any information on the machines.
tblMachinesServiced contains a list of each machine that was serviced for each service order. It contains the ServiceOrderNo and the MachineID number.
What I want is to be able to extract a list of machines that were not serviced between 2 dates. What I end up getting is a list of machines that were serviced outside of the date range I provide.
For instance, say machine A was serviced in 2013 and 2015 but not in 2014. And say machine B was serviced in all 3 years. When I try to extract my list of machines not serviced in 2014 I end up with a list that contains machine A-2013, A-2015, B-2013 & B-2015. But what I need is just machine A-2014, since that machine wasn’t serviced in 2014.
I’ve tried several different queries but here is an example:
SELECT tblMachines.MachineID,ServiceMachines.ServiceDate FROM tblMachines LEFT JOIN (SELECT MachineID, ServiceDate FROM tblServiceOrders, tblMachinesServiced WHERE tblServiceOrders.ServiceOrderNo=tblMachinesServiced.ServiceOrderNo ) ServicedMachines ON tblMachines.MachineID=ServicedMachines.MachineID WHERE YEAR(ServiceDate) != '2014'
I understand why it returns the records that it does, but I'm not sure how to get what I want, which is a list of machines not serviced in 2014.
I have results from a query that have anywhere from 1-4 results. I have a subid of 1-4 which is grouped by a certain criteria. so for example...
id subid text 1 processing A records 2 1000 records processing 3 importing A records 4 1000 records processed 1 processing B records 2 500 records processing 3 importing B records 4 1000 records processed
Here is what my desired output is giving each group of subids 1-4 an id to be grouped together.
id subid text 1 1 processing A records 1 2 1000 records processing 1 3 importing A records 1 4 1000 records processed 2 1 processing B records 2 2 500 records processing 2 3 importing B records 2 4 1000 records processed
I have a query below to show all the records with joining these two tables.
SELECT DISTINCT B.BF_ORGN_CD, B.LEV5, A.BF_ACTY_CD FROM BF_ORGN A INNER JOIN BF_ORGN_CNSL_TBL B ON A.CD=B.BF_ORGN_CD WHERE A.BF_ACTY_CD IS NOT NULL ORDER BY B.BF_ORGN_CD,A.BF_ACTY_CD
My goal is only to show all the duplicate records.
Bf_ORGN_CD LEV5 BF_ACTY_CD AC_21234_2 AC_21200_1 402 AC_21236_2 AC_21200_1 402 AC_21238_2 AC_21200_1 402 AC_29000_1 AC_29000_1 802 ---> NOT SHOW (ONLY 1 RECORD) AC_29988_1 AC_29988_1 801 ---> NOT SHOW (ONLY 1 RECORD)
I am using the following views from two db's to find records that don't match.
My question is can I have output of fields from the second table
SELECT distinct T1.[last name],t1.[first name],ENum FROM ECLINICIAN_Info T1 WHERE NOT EXISTS(SELECT * FROM ACLINICIAN_Info T2 WHERE t1.Enum = t2.Anum and t1.[last name] = t2.lname and t1.[first name] = t2.Fname)
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField from MyOptions where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField from MyTimetable where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
I have a table of Customers & their data in about 20 Columns.
I have another table that has potential Customers with 3 Columns.
I want to append the records from Table 2 onto Table 1 to the Columns with the same names.
I've thought of using UNION ALL or Select Insert but I'm mainly stuck on the most efficient way to do this.
There is also no related field that can be used to join the data as these Customers in table 2 have no Customer ID yet as they're only potential Customers.
Can I just append the 3 columns from Table 2 to the same 3 columns in table 1?
I'm using MS SQL Server 2008 and I'm trying to figure out if it is possible to identify what tables / columns contain specific records.
In the example below information generated for the end user, so the column headers (Customer ID, Customer, Address, Phone, Email, Account Balance, Currency) are not necessarily the field names from the relevant tables, they are simply more identifiable headers for the user.
Customer ID CustomerAddress Phone Email Account Balance Currency js0001 John Smith123 Nowhere Street555-123-456 jsmith@nowhere.com-100 USD jd2345 Jane Doe 61a Down the road087-963258 jdoe@downthe road.com-2108 GBP mx9999 Mr X Whoknowsville 147-852369 mrx@whoknows.com0 EUR
In reality the column headers may be called eg (CustID, CustName, CustAdr, CustPh, CustMail, CustACBal, Currency).
As I am not the generator of this report, I would like to know whether or not it is possible to identify the field names and / or what tables they exist in, if I were to used the report info to search for it. For example, could I perhaps find out the field name and table for "jd2345" or for "mrx@whoknows.com", because the Customer ID or Email may not be what the actual fields are called.
I'm not a DB admin and I don't have rights to do a stored procedure on the server. I'm guessing what I want is not so simple to do, but is it possible to do via a query?
CREATE TABLE [dbo].[DR_Test]( [source_item_id] [int] NOT NULL, [source_line_no] [int] NULL, [buyer_id] [int] NOT NULL, [seller_member_id] [int] NULL,
[code]...
the table contains more than 80 million records so when i fetch the data using buyer_id & timezone its taking lot of more than 1 hours or so....& where buyer_id is not unique.how to fetch the data fast or need to change the structure of the table
I have a query needs to look for 5 records data in a table. Basically i need to hardcode. Below is my query which didn't work out.
select BF_ORGN_CD, BF_BDOB_CD, BF_TM_PERD_CD,data from BF_DATA WHERE (BF_ORGN_CD,BF_BDOB_CD,BF_TM_PERD_CD) in ***** i guess this is the wrong query**** ('A1', 'B1', 'C1') ('A2', 'B2', 'C2') ('A3', 'B3', 'C3') ('A4', 'B4', 'C4') ('A5', 'B5', 'C5')
but if i use the query below it will generate more records than these 5 records
select BF_ORGN_CD, BF_BDOB_CD, BF_TM_PERD_CD,data from BF_DATA WHERE (BF_ORGN_CD) in ('A1', 'A2', 'A3', 'A4', 'A5') and (BF_BDOB_CD) in ('B1', 'B2', 'B3', 'B4', 'B5') and (BF_TM_PERD_CD) in ('C1', 'C2', 'C3', 'C4', 'C5')
I am using a SQL Server Agent jobs that run each morning to update the records in a table to match what they should be for that day. I built them and tested it using a test table called "testtable1". It worked fine. But when I switched over to our production table, it fails saying the table has to be decaled. What would be the difference. The production table has a "@" in front of the name, is that causing issues?
USE [Live_build] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO BEGIN DELETE FROM @ZIPLIST INSERT INTO @ZIPLIST SELECT * FROM tblZip3DSWed; END
I've a excel spreadsheet with 650 records with unique PONumbers. I need to pull data from SQL server based on the PONumbers. I don't want to run select statement 650 times. How do I retrieve the records in efficient way?
I have groups of records in a table, and I would like to set a necessary condition on each group. The condition is that EXACTLY ONE of the records in each group has a flag field set to True (bit = 1). I can naturally write triggers for update, insert and delete events that test for such a condition.
Something along the lines of this condition:
(select count(ClovekAutoID) from TableOfClovekNames tCN where JeHlavni = 1 group by ClovekAutoID having COUNT(JeHlavni ) > 1) = 1In fact,
I tried this just on whim, but naturally, the SS engine told me to go roll my hoop, that subqueries are not allowed in constraint expressions.
Below is the code for two data sets and I can't seem to get my head around the issue. I need to find the number of 'ER' visits and 'IN' visits, separately, in dbo.VisitData for the 'Active' patients in dbo.PatientStatus. So, consider patient 69. He is Active on 5/5/2014 but becomes Inactive on 9/15/2014. I only want to count the number of visits ER or IN that are between those dates. In addition if patient 69 becomes active again after 9/15/2014, I need to capture that data as well. Patients can change there status multiple times.
i have a table containing the top 5 teams points for the current season using the rank function, and as well as their points for last season.the only problem is that i want to add a sequential ranking to the table so that if there are 2 or more teams with the same score this season, they are ranked based on their points last season;
League -- Team - Season -- Points League 1 AAA 2013-14 90 League 1 BBB 2013-14 80 League 1 CCC 2013-14 75 League 1 DDD 2013-14 70 League 1 EEE 2013-14 65 League 1 AAA 2014-15 90 League 1 BBB 2014-15 80 League 1 CCC 2014-15 80 League 1 DDD 2014-15 70 League 1 EEE 2014-15 65
