I have 3 tables, a supplier table, a types table and a relationship table between the two..I want to build a query that put the different types in columns, and use a Boolean value to identify if the supplier supplies that type.
CREATE TABLE #Suppliers(
id INT,
nam NVARCHAR(50)
[code]...
It's possible to do this in "one" query?
I have a person table
CREATE TABLE Person
(
PersonID INT
Name varchar(50),
HireDate datetime,
HireOrder int,
AltOrder int
)
Assume I have data like this
INSERT INTO Person VALUES(1, 'Rob', '06/02/1988', 0, 0)
INSERT INTO Person VALUES(2, 'Tom', '05/07/2016', 0, 0)
INSERT INTO Person VALUES(3, 'Phil', '01/04/2011', 1, 0)
INSERT INTO Person VALUES(4, 'Cris', '01/04/2011', 2, 0)
INSERT INTO Person VALUES(5, 'Jen', '01/04/2011', 3, 0)
INSERT INTO Person VALUES(6, 'Bill', '01/05/2011', 0, 0)
INSERT INTO Person VALUES(7, 'Ray', '01/23/2012', 0, 0)
I'm trying to simplify my requirement... providing the input of HireDate, HireOrder, and AltOrder, I need to be able to pick up the next person
For ex:, if I provide input, HireDate: 06/02/1988, HireOrder:0, AltOrder:0, the return value expected is "Tom" because he is the next person after the provided input.
For ex:, if I provide input, HireDate: 05/07/2016, HireOrder:0, AltOrder:0, the return value expected is "Phil" because he is the next person after the provided input. Though Phil and Cris have same dates, their HireOrder takes precedence in this case. If they also have same HireOrder, AltOrder would be coming in picture to determine next person
Another ex: if I provide input, HireDate: 01/04/2011, HireOrder:1, AltOrder:0, the return value expected is "Cris" because she is the next person after the provided input. Here hireorder determines.
If I provide, HireDate: 01/23/2012, HireOrder:0, AltOrder:0, as there is no person after this, I should be able to pick the first person on the list - in this case Rob.
I can write some business logic in front-end, but I thought it would be good, if I can move this to a stored procedure which can return me the PersonID for optimal performance.
I have tried writing various conditions but couldn't achieve a query that meets all my requirements here.
I'm even fine if my last condition is not met (returning the first person in the list, in case no one is available after the provided input).
I have a requirement to add new calendar columns from Jan to Dec in an existing SSRS report based on certain conditions.
The report uses a simple query at the moment.
SELECT [PROJECT ID]
,[PROJECT]
,[STATUS]
,[ACTUAL START]
,[ESTIMATED END]
FROM Projects
The requirement to add the new calendar columns is based on the following conditions:
1. If the Status value is “2” then for each Month in the current year, shade in green for the particular current month
For example, if for a particular Project for month/year of April 2015
Year/Month(Actual Start date) is < = April 2015
and Year/Month(Estimate End date) > = April 2015, then April should be shaded green.
2. The logic needs to be repeated from each month in the current year.
3. Some Estimate and Actual dates run from previous year, or into next year, so year also needs to be taken into consideration.
find the DDL and sample data below.
CREATE TABLE [dbo].[Projects](
[PROJECT ID] [nvarchar](255) NULL,
[PROJECT] [nvarchar](255) NULL,
[STATUS] [nvarchar](255) NULL,
[ACTUAL START] [datetime] NULL,
[ESTIMATED END] [datetime] NULL
) ON [PRIMARY]
insert into Projects
values('1','TFD','2','2015-01-31 00:00:00.000','2015-12-31 00:00:00.000')
insert into Projects
values('2','JUH','1','2015-02-01 00:00:00.000','2015-02-27 00:00:00.000')
insert into Projects
values('3','REFFD','2','2015-01-31 00:00:00.000','2015-11-30 00:00:00.000')
[code].....
how to rewrite the existing query to add new calendar columns in the report and then in the SSRS output shade green based on the above condition.
Hi,
Im exporting an ms-excel file, then I use a lookup transformation to get a field from a SQL Server 2005 table. The Lookup transformation editor, after selecting the table, shows a warning that says:
at least one mapping between a column from available input columns ans a column from available lookup columns must be defined on the columns page.
So I try to make a relationship in the Lookup transformation editor's column tab where I find the Available input columns and the available lookup columns but I get the following error:
The following columns cannot be mapped:
[Department, DEP_CLEGALCODE]
One or more columns do not have supported data types, or their data types do not match.
The field in SLQ Server is varchar(10) and the input field is a derived column transformation; I have tried different Data Types but I always have the same error.
The DataFlow is: ExcelSource --> Derived Column --> Lookup --> Flat file destination
thanks.
How I can build a dynamic temp table based upon the dynamic coulmn info from the other table? Please see my attached file as an example. Thanks!
J827
SQL Server 2000, ASP.Net 1.1
I've been writing this stuff for a while, and can't seem to come to the
conclusion of how I should be retrieving data and assigning this data
to variables.
Since i'm using SQL Server, I'm convinced that I should be using the
datareaders GetSqlDouble (or whatever) function, but this would mean i
need my local variables to be one of the SQL types. The problem
with that is, that there will have to be lots of conversions done by me
to be able to use a SQL type in my application.
For instance, I have a class where i'm retrieving dates. In order
to retrieve them correctly (Null values included), I need to retrieve
them with GetSqlDateTime(), then when it comes time to display the date
in a table, i must first check for nulls, then convert to a
string. This seems to be very cumbersome. Would I be better
off just using GetDateTime(), and the .ToString method, and ignoring
Sql Types all together?
so, basically, how are you guys using your sql server data? with
the supplied sql types, and doing all of the post-processing work
manually? I feel like i'm having trouble conveying my
issue...hopefully someone knows what i mean....i'd just like some
direction to save trouble in the long run, since i feel like there's
got to be a better way...
Confused!
Thanks,
JJ
Recently I have come across a requirement where i need to design a table.
There are some columns in table like below with DECIMAL Datatype:
BldgLength
BldgHeight
BldgWeight
Based on my knowledge, i know that values before Floating-Point will not be more than 4 digits.
Now as per MSDN,
Precision => 1 - 9
Storage bytes => 5
so i can create column as:
BldgLengthDECIMAL(6,2) DEFAULT 0
OR
BldgLengthDECIMAL(9,2) DEFAULT 0
Now while reading some articles, i came to know that when we do some kind of operation like SUM Or Avg, on above column then result might be larger than current data type.
So some folks suggested me that i should keep some extra space/digits considering above MATH functions, to avoid an Arithmetic Over Flow error.
So my question is what should be value of DataType for above column ?
I have a database with 1million+ records in and i'm trying to collect the median values of column(2) for all distinct values in column (1)
Example DB:
Column 1 Column 2
978555 500
978555 502
978555 480
978555 490
978324 1111
978324 1102
978311 122
978311 120
978994 804
978320 359
and I need it to display on SELECT as
column 1 column 2
978555 495
978324 1106
978311 121
978994 804
978320 359
Is this possible on 2008 R2?
I have two tables: "Users" and "Messages".
The Messages table has a "UserFromID" and an "UserToID", indicating a message was sent from a user to a user.
The Users table has a UserID, FirstName, LastName and UserGroup.
Im trying to get all messages sent by or to a user in a specific group, with the First and Last names of the sending and receiving users.
Here is my tables and data:
CREATE TABLE tmp_users (userID INT, GroupID INT, UserName VARCHAR(10), FullName VARCHAR(100))
CREATE TABLE tmp_Messages (aKey INT IDENTITY, FromUserID INT, ToUserID INT, msg VARCHAR(100))
INSERT INTO tmp_users VALUES (1,1,'John','John Smith')
INSERT INTO tmp_users VALUES (2,1,'Jane','Jane Doh')
INSERT INTO tmp_users VALUES (3,1,'Bob','Bob Newheart')
[code]....
The query Im running so far is wrong, but here it is...
SELECT t.FromUserID, t.ToUserID, t.msg, u.UserName AS UserFrom,
u.GroupID AS FromGroup, u2.UserName AS UserTo, u2.GroupID AS ToGroup
FROM tmp_Messages t
LEFT JOIN (SELECT UserID, GroupID, UserName FROM tmp_users WHERE GroupID = 3) u
[Code] .....
im missing the details of one of the users.I know what the problem is, I just cant figure out how to get this working without using temp tables, which I cant do in the production version.
how to use like operator select statement to retrieve multiple column names in sql server DB...for ex: I have a table say employees where in I want to get all column names like emp_,acc_ etc using '%' And what is this below query used for?
SELECT column_name as 'Column Name', data_type as 'Data Type',
character_maximum_length as 'Max Length'
FROM information_schema.columns
WHERE table_name = 'tblUsers'
I am struggling with doing what should be a fairly simply transpose of columns to rows. I have found some examples but nothing I could get working.
I have data in the form of:
Brand, Model, Color1, Color2, Color3, Color4, Color5, Color6
Honda, Accord, Red, Grey, Black, White,,,
Toyota, Corolla, White, Black,,,,,
The output should look like:
Brand, Model, Color
Honda, Accord, Red
Honda, Accord, Grey
Honda, Accord, Black
Honda, Accord, White
Toyota, Corolla, White
Toyota, Corolla, Black
how to encrypt a either columns or tables in SQL server (2008 R2).
Do I need to encrypt a whole table of can I encrypt certain columns of a table ?
Is there a way to combine 2 columns of different types for example varchar and decimal?
column1 = varchar, column2 = decimal
For example:
SELECT column1 + ' - ' + column2 AS CombineColumn FROM TABLE1
Without getting "Error converting data type varchar to numeric."
Thanks for any help!
What are attributes in columns that are not types?
View 1 Replies View RelatedHi,
I am having the following problem AFTER converting to VS2008 from VS2005 and SQLCE 3.5 from 3.01:
SQL CE 3.5 db file has a table called Court0 with various columns of type float. I populate the values by copying floats from another table/tables. I do this via ado.net using this code snippet:
foreach (DataColumn column in this.mycourtsDataSet1.Tables[tableName].Columns)
{
string columnName = column.ColumnName.ToString();
string columnValue = aRow[0][columnName].ToString();
object cValue = aRow[0][columnName];
if (columnName.Remove(1) == "T" && !string.IsNullOrEmpty(columnValue))
{
// Add the value to the Court0 table.
DataRow[] bRow = this.mycourtsDataSet1.Tables["Court0"].Select("BookingPeriod = '" + columnName + "'");
if (bRow.Length > 0)
{
double colValue = Convert.ToDouble(cValue);
//bRow[0][tableName] = Convert.ToInt32(columnValue);
========> bRow[0][tableName] = colValue; <==== colValue is '1055.01'
}
}
}
}
This works fine in VS2005/CE3.01 BUT not in VS2008/CE3.5
In CE3.5, the value entered into the cell looses it's decimal value.
For example, '1055.01' becomes '1055.0' in CE3.5 .
Can someone explain to me why the conversion stuffs up in CE3.5 and what do I do to fix it.
Thanks,
I've got several columns in my database stored as money type. For my purposes, when reporting this data I need to truncate trailing zeros after the decimal. I know I can create a function to do this. I think given the quantity of columns and the numerous queries accessing them, I will undoubtedly forget to use a function everywhere needed.
I thought user defined types may be a solution. I've never used them before. I would still like to store the data as a money type, but anytime it is accessed it would be formatted. Can user defined types do this, or is there a better way?
I'm able to successfully import data in a tab-delimited .txt file using the following statement.
BULK INSERT ImportProjectDates FROM "C: mpImportProjectDates.txt"
WITH (FIRSTROW=2,FIELDTERMINATOR = ' ', ROWTERMINATOR = '')
However, in order to import the text file, I had to add columns to the text file to match the columns that exist in the table. The original file is an export out of another database and contains all but 5 columns from my db.
How would I control which column BULK INSERT actually imports when working with a .txt file? I've tried using a FORMAT FILE, however I kept getting errors which I tracked down to being a case of not using it with a .txt file.
Yes, I could have the DBA add in the missing columns to the query from the other DB to create the columns, however I'd like to know a little bit more about this overall.
The Delete-Insert update happens for key columns of Indexes and that makes sense.
But I am confused that why Delete-Insert update happens for Included columns of nonclustered indexes, where I expected them to be InPlace updates ???
I have multiple databases in the server and all my databases have tables: stdVersions, stdChangeLog. The stdVersions table have field called DatabaseVersion which stored the version of the database. The stdChangeLog table have a field called ChangedOn which stored the date of any change made in the database.
I need to write a query/stored procedure/function that will return all the database names, version and the date changed on. The results should look something like this:
DatabaseName DatabaseVersion DateChangedOn
OK5_AAGLASS 5.10.1.2 2015/01/12
OK5_SHOPRITE 5.9.1.6 2015/01/10
OK5_SALDANHA 5.10.1.2 2014/12/23
The results should be ordered by DateChangedOn.
Currently I have a table that looks like the one below and I need to concatenate the description column and keep the rest of the row the same.
current:
IDSeq Desc DateOpen DateClose
1 AA description 1 1/1/2015 12/31/2015
1 AB description 2 1/1/2015 12/31/2015
Desired outcome:
ID Desc DateOpen DateClose
1 description 1,description 2 1/1/2015 12/31/2015
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?
I have a ipaddress column is there where i need to split the column into two columns because of
values like below
172.26.248.8,Fe80::7033:acba:a4bd:f874
172.26.248.8,Fe80::7033:acba:a4bd:f874
172.26.248.8,Fe80::7033:acba:a4bd:f874
I have written the below query but it will throw some error.
select SUBSTRING(IPAddress0, 1, CHARINDEX(',', IPAddress0) - 1) as IPAddress0
from IPADDRESS
error:
Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
I would like to pull data from two seperate columns based on the vaule for MakeFlag. So if MakeFlag = 0 I would like the description to show but anything else I would like catalog description to show up.
DECLARE @MyVari varchar(20)
SELECT [ProductID]
,[prod].[Name]
,[ProductNumber]
,[MakeFlag]
[Code] ....
I have attached SQL File which Gives me the below resultset Excel.xlsx
But the problem is i am not able to round off the dynamic columns in side my PIVOT, how to rewrite the dynamic query.
I have a matrix report with STORES in the row group and DATES in the column group. The table sums on SALES. The DATES column is formatted like =format(Fields!DATES.Value, "MMM yyyy"). The table also has 2 parameters @Start and @End. This all works great but I then added a child report so that the user can click on the SALES value for any sale by month and store. The child report uses the @Start and @End parameters from the original report but this is where I run into problems.
Rather than bringing me the sales details for a particular store and month it brings back everything from the time period selected with the original date parameters. So say I originally selected 2015-01-01 to 2015-06-30 with the parameters when I select on FEB 15 in my matrix report I get Febs data along with all the other months ie Jan-Jun 15. The DATES fields in both reports are in the same date format - in fact both reports use exactly the same dataset.
I realize it's something to do with the formatting of the DATE field not being recognized in the linked report.
Does including non-key columns work for the performance of an index?
View 8 Replies View RelatedIs there a good way to add columns to a table type?
I built several procs which make use of table-valued-parameters, and they work pretty nicely, until I need them to accept additional columns. Then I have to drop all the procs that use them, alter the types, and rebuild all the procedures, which is a huge pain in the rear.
Is there any good way (built in, or custom) to alter the def of a table type that's used as a parameter to multiple stored procedures?
We have a table in DB2, which has a column1 (Data type: - decimal (10, 0)) and column2 (Data type: - varchar (70)), I need to concatenate these two columns into one while displaying output
Procedure I followed:--
Tried to convert column1 to varchar using cast function so that I could concatenate both columns. However I ended up in getting the following error message
DB2 SQL error: SQLCODE: -461, SQLSTATE: 42846, SQLERRMC: SYSIBM.DECIMAL; SYSIBM.VARCHAR
Message: A value with data type "SYSIBM.DECIMAL" cannot be CAST to type "SYSIBM.VARCHAR".
Please do let me know where I went wrong
Thanks
Pratibha Surya
/*Subject: How to build a procedure that returns differentnumbers of columns as a result based on a parameter.You can copy/paste this whole post in SQL Query Analyzeror Management Studio and run it once you've made surethere is no harmful code.Currently we have several stored procedures which finalresult is a select with several joins that returns manycolumns (150 in one case, maybe around 50 the average).We have analyzed our application and found out that mostof the time not all the columns are used. We haveidentified 3 different sets of columns needed indifferent parts of the application.Let's identify and name these sets as:1- simple set, return the employee list for example2- common set, return the employee information (whichinclude the simple set)3- extended set, return the employee information (whichinlude the common set which itself includes the simpleset) + additional information from other tables, maybeeven some SUM aggregates and so on (I don't know forexample, how much sales the employee did so far).So the bigger sets contain the smaller ones. Please keepreading all the way to the bottom to better understandtechnically what we are trying.Here is a code sample of how our current procedureswork. Please note that the passing parameter we can eitherpass a Unique Identifier (PK) to retrieve a single record,or if we pass for example -1 or NULL we retrieve all theemployee records.*/create table a ( apk int primary key, af1 int, af2 int, af3 int, af4int, af5 int, af6 int)create table b ( bpk int primary key, bf1 int, bf2 int, bf3 int, bf4int, bf5 int, bf6 int)create table c ( cpk int primary key, cf1 int, cf2 int, cf3 int, cf4int, cf5 int, cf6 int)create table d ( dpk int primary key, df1 int, df2 int, df3 int, df4int, df5 int, df6 int)insert a values (1,1111,1112,1113,1114,1115,1116)insert a values (2,1211,1212,1213,1214,1215,1216)insert a values (3,1311,1312,1313,1314,1315,1316)insert a values (4,1411,1412,1413,1431,1415,1416)insert a values (5,1511,1512,1513,1514,1515,1516)insert a values (6,1611,1612,1613,1614,1615,1616)insert b values (1,2111,2112,2113,2114,2115,2116)insert b values (2,2211,2212,2213,2214,2215,2216)insert b values (3,2311,2312,2313,2314,2315,2316)insert b values (4,2411,2412,2413,2431,2415,2416)insert b values (5,2511,2512,2513,2514,2515,2516)insert b values (6,2611,2612,2613,2614,2615,2616)insert c values (1,3111,3112,3113,3114,3115,3116)insert c values (2,3211,3212,3213,3214,3215,3216)insert c values (3,3311,3312,3313,3314,3315,3316)insert c values (4,3411,3412,3413,3431,3415,3416)insert c values (5,3511,3512,3513,3514,3515,3516)insert c values (6,3611,3612,3613,3614,3615,3616)insert d values (1,4111,4112,4113,4114,4115,4116)insert d values (2,4211,4212,4213,4214,4215,4216)insert d values (3,4311,4312,4313,4314,4315,4316)insert d values (4,4411,4412,4413,4431,4415,4416)insert d values (5,4511,4512,4513,4514,4515,4516)insert d values (6,4611,4612,4613,4614,4615,4616)gocreate procedure original_proc @pk int asif @pk = -1set @pk = nullselecta.af1, a.af2, a.af3, a.af4, b.bf1, b.bf2, b.bf3, b.bf4, c.cf1, c.cf2,c.cf3, c.cf4, d.df1, d.df2, d.df3, d.df4fromajoin b on a.apk = b.bpkjoin c on b.bpk = c.cpkjoin d on c.cpk = d.dpkwherea.apk = ISNULL(@pk, a.apk)goexec original_proc 1go/*Currently the above SP is a single SP that is basicallyreturning ALL possible needed data. However most of thetime we might need to call and retrieve a simple employeelist.So we thought about modifying the stored procedure byadding an extra parameter that will indicate which setof columns to return.For modifying the stored procedure in order to get avariable name of columns returned and avoidingrepeating code, we built 4 objects: the storedprocedure being called, one table function and 2 views.One table function so that we are able to pass a parameter.The views since they do not accept parameters they arealways joined at least with the inline table function.The stored procedure generates in its body a dynamicSQL statement, where it queries the table function andthe views, depending which set is required. Here is acode sample of our current design (you need to run theprevious code in order for this to work).*/create function _1_set(@pk int)returns tableas return(select a.apk, a.af1, a.af2, a.af3, a.af4, b.bf1, b.bf2from ajoin b on a.apk = b.bpkwhere a.apk = ISNULL(@pk, a.apk))gocreate view _2_set asselect b.bpk, b.bf3, b.bf4, c.cf1, c.cf2from bjoin c on b.bpk = c.cpkgocreate view _3_set asselect c.cpk, c.cf3, c.cf4, d.df1, d.df2, d.df3, d.df4from cjoin d on c.cpk = d.dpkgocreate procedure new_proc @pk int, @set int asdeclare @sql nvarchar(4000)if @pk = -1set @pk = nullset @sql = 'select * from _1_set(@pk) fs 'if @set 1set @sql = @sql + 'join _2_set ss on fs.apk = ss.bpk 'if @set 2set @sql = @sql + 'join _3_set ts on ss.bpk = ts.cpk 'exec sp_executesql @sql, N'@pk int', @pkgoexec new_proc 1, 3go/*For executing the new procedure, we pass parameter 1for the smaller set, 2 for the medium size set or 3for the complete set.For example when we want to retrieve the common setwe pass the Unique Identifier of the employee to theSP and then we pass the type of set we want to useas the second parameter (1 for simple set, 2 forcommon set and 3 for extended set).The SP has the IF and dynamic SQL to add more JOINs.We would like to know what you think of this approachand if you know a simpler way of doing it.For cleaning up the test objects run the following code.*/drop procedure original_procdrop procedure new_procdrop function _1_setdrop view _2_setdrop view _3_setdrop table adrop table bdrop table cdrop table dAs always I would appreciate any feedback, opinion,comments, ideas and suggestions.Thank you
View 9 Replies View RelatedOur front end saves all IP addresses used by a customer as a comma separated string, we need to analyse these to check for blocked IPs which are all stored in another table.
A LIKE statement comparing each string with the 100 or so excluded IPs will be very expensive so I'm thinking it would be less so to split out the comma separated values into tables.
The problem we have is that we never know how many IPs could be stored against a customer, so I'm guessing a function would be the way forward but this is the point I get stuck.
I can remove the 1st IP address into a new column and produce the new list ready for the next removal, also as part of this we would need to create new columns on the fly depending on how many IPs are in the column.
This needs to be repeated for each row
SELECT IP_List
, LEFT(IP_List, CHARINDEX(',', IP_List) - 1) AS IP_1
, REPLACE(IP_List, LEFT(IP_List, CHARINDEX(',', IP_List) +0), '') AS NewIPList1
FROM IpExclusionTest
Results:
IP_List
109.224.216.4,146.90.13.69,146.90.85.79,46.208.122.50,80.189.100.119
IP_1
109.224.216.4
NewIPList1
146.90.13.69,146.90.85.79,46.208.122.50,80.189.100.119
Is there a way to permanently change the order of the columns in Job Activity Monitor?
I'd like to move Duration to the right of Step Name, but this only lasts so long as I have JAM open. Once I close it and re-open, JAM goes back to its default column order. Google gives me nothing but the temporary "drag and drop" method that I already know about.
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.e.
Pass:
'Hello', 'abc123'
Fail:
'太平市', 'abc太123'