Our database defines the long_value column as nvarchar(max). I want to find out which rows actually contain non-ASCII characters in that column, but this clause also returns rows with only ASCII characters:where long_value like (N'%[' + nchar(128) + N'-' + nchar(65535) + N']%')
What am I doing wrong?
Hi,
I have a problem with BULK INSERT. I created the following table:
Code Snippetcreate table Test
(id char(4), name nvarchar(16), last char(1))
I am trying to bulk insert data from ASCII (not unicode) file with only two rows:
0011First name
0018Second name
Since it is a fixed length file, I am using the following format file:
Code Snippet
8.0
3
1 SQLCHAR 0 4 "" 1 ID HEBREW_CI_AS
2 SQLCHAR 0 16 "" 2 NAME HEBREW_CI_AS
3 SQLCHAR 0 0 "
" 3 Last HEBREW_CI_AS
With bcp utility everything works just fine!
Code Snippet
bcp Demo.dbo.test in c: est -T -f c: est.fmt
But when I use BULK INSERT in the following form:
Code Snippet
BULK INSERT Test FROM 'c:Test'
WITH
(
FORMATFILE='c:Test.fmt',
CODEPAGE='OEM'
);
I am getting error
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 2 (name).
Now, one interesting thing: if I change the name field from nvarchar to varchar, it is working with BULK INSERT as well.
Can anybody explain what is going on here?
I am using MS SQL 2000 and MSDE
Thanks in advance,
Eugene.
Is there any way to convert Unicode (NVarchar) to Ascii (Varchar)?
If I just use cast , the result will become ???.
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'
I’m getting ASCII characters in one column of my table. So I want to replace same column value in NON ASCII characters.
Note – values in column must be same
Hello, my first post :)
I'm currently working on a project that involves IP checking, here's my scenario:
A user enters an IP address of 10.174.55.65 let's say, I want to somehow be able to check and see if this IP address is in between column A (It's called ip) which consists the IP data of 10.174.0.0 and column B (It's called EndingIp) which has the IP data 10.174.255.255, so generally speaking I'm checking for the 3rd and 4th spots within the IP address and I'm trying to see if it's between the IP's in column A and B.
The thing is these are all strings, so how do I approch a stiuation like that within SQL?
Btw, I attached a picture of the table to be more clear.
Thanks in advance for the advice...
Hello all,
I am trying to think my way through a solution which I believe others have probably come across... I am trying to implement a matching routine wherein I need to match an address against a high value and a low value (or, for that matter an input date vs. a start and end date) to return the desired row ... i.e. if I were to use a straight vb program I would just use the following lookup:
"SELECT DISTINCT fire_id, police_ID, fire_opt_in_out, police_opt_in_out FROM ipt_tbl " & _
" WHERE zip_code = @zip_code AND addr_prim_lo <= @street_number AND addr_prim_hi >= @street_number " & _
" AND addr_prim_oe = @addr_prim_oe AND street_pre = @street_pre AND street_name = @street_name " & _
" AND street_suff = @street_suff AND street_post = @street_post " & _
" AND (expiry_date = '' OR expiry_date = '00000000' OR expiry_date > @expiry_date)" & _
" GROUP BY fire_ID, police_ID, fire_opt_in_out, police_opt_in_out"
My question, then, is how would you perform this type of query using a lookup / merge join or script? I have not found a way to implement a way to set the input columns? I can set the straight matches without a problem, i.e. lookup zip code = input zip code, but can't think of the correct way to set comparisons, i.e. lookup value 1 <= input value AND lookup value 2 >= input value
Any suggestions?
thanks for your time...
Hi,
I have a table name bla.
PKEY id, int, NOT NULL
group, int
name, string, NOT NULL
how do I compare them with int?
for example the following data.
1, NULL, 'freelance'
2, 1, 'group1'
3, 2, 'group2'
select * from bla where group<>1 <-- this fails?
What is the proper SQL Statement for this?
Regards,
Max
Hi,
i need to select from a table transact where one of the coulmn values has to be equal to (1and 2 and 3).
e.g: column in (1,2,3) would give me what "OR" would do,
I've been working with T-SQL in a MSSQL Server Management Studio (2005) for about a week now. I've been trying to convert some horribly written VB code from a MS Access DB over to SQL so it can be automated on a SQL backend.
Most of the learning process and coding has gone surprisingly well. The problem is with comparing some data to determine which one needs to be flagged.
Three tables to note in bold, with notable fields in italics below them:
EmployeeData
HRID (identity)
ResourceAllocation
ID (identity)
[Last Name] (linked to HRID)
Project
[Resource Start Date]
[Resource End Date]
[Percent Utilization]
tblHCvalues
RAID (linked to ResourceAllocation.ID)
a monthyear and quarteryear for every month and quarter from 2012-2014. IE january12, february12, 1q12, 2q13, etc...
And yes, there are probably a thousand ways to optimize that tblHCvalues, but I'll ask about that later. Just work with the structure I have
Here's how it works: Each employee's data and unique HRID is in the EmployeeData tableAn employee can be on one or multiple projects at any timeThose projects are stored per project in the ResourceAllocation table with a link to the Employee's HRID, and all the other information listed aboveEven though an employee might be on two projects, they can only count for headcount on one project.
We use rules that compare the percent of work being done on a project, and the start and end dates of the employee (resource) on that project to determine which project should be counted for Headcount. The code uses a cursor to go through each HRID, and then pull up all the ResourceAllocation records associated with it.Run the rules to determine which ResourceAllocation record counts toward headcountA stored procedure then runs that fills out the tblHCvalues in the way we want for the project we want
All of it works, except for the rules that compare the things, so that's what I want to focus on in this thread. How do I write these rules:
Here are the rules, and they should work for any number of multiple resource allocations for one employee:
Choose the ResourceAllocation with the greatest [Percent Utilization]If the top ResourceAllocations have equal [Percent Utilization], choose the ResourceAllocation with the earliest [Resource Start Date]If the [Percent Utilization] and the [Resource Start Date] are equal, choose the latest [Resource End Date]If all three fields are equal, choose the first ResourceAllocation (aka, screw it and pick one at random)
I'm sure I could use a bunch of IF statements to compare it all, but even that is complicated to think about. There has to be an easier way, right?
Is there a way to compare tables in 2 different databases to find out if they have the same indexes and column names and keys. Or maybe a tool i dont know about.
View 1 Replies View RelatedHi,
My company is starting to use nvarchar columns in our database products. We just found out that, suppose table T1 has a my_nvarchar_col column, and there is a row containing a unicode text say "some Chinese", if you want to select that row, you have to append "N" in front of the unicode constant in the "WHERE" clause. That is:
select * from T1
where my_nvarchar_col = N'some Chinese'
will return that row, while
select * from T1
where my_nvarchar_col = 'some Chinese'
will return NOTHING.
This brings us a huge problem - we have tens of thousands of such queries in our existing PowerBuilder code base. Do we have to go through all of them to add "N" to the "WHERE" clause? Is there a way we can set some attribute of SQL Server so that we do not need to do that?
Project stalled and I am under extreme pressure so please help ASAP!!
How to resolve the below task
create table #temp ( idx int identity(1,1), col1 int, col2 int )
Here i want a flag success or fail on basis of below conditions.
I need to take all the col1 values and then i need to compare to each other.
if any difference found, i need to check difference more than 30, then it should raise the flag as "Failure".
if all the col1 values are ok , then we need to check Col2 values same as above.
--case 1
insert into #temp(col1,col2)
select 16522,18522
union all
select 16522,18522
union all
select 16582,18522
--select * from #temp
--truncate table #temp
Because of difference in col1 values . the value of flag should be fail.
--case 2
insert into #temp(col1,col2)
select 16522,18522
union all
select 16522,18522
union all
select 16522,17522
Here also the col1 is ok but col2 values have difference so it should be Fail.
Otherwise it should be success.
Let me see if I can explain my situation clearly.I have a table with the columns:answer_id, question_id, member_id, answer- answer_id is the primary key for the table.- question_id relates to another table with questions for a user. Thetable holds the question and the possible choices in a varchar fieldseparated by a delimiter.- member_id is self-explanatory- answer is a varchar field of all the choices the user selected,separated by a delimiter.Here is my problem.I am trying to search all members that have answered, say, question_id= 2 where they selected 'brown' as one of their choices.i can do this if they selected ONLY that item, but not multiple items.The problem is this portionanswer in(select valu from dbo.iter_intlist.....I need this to be something like....function_to_return_all_separated_answers(answer) in(select valu from dbo.iter_intlistThe current way, it is only returning members that have an answer'Brown', not 'Brown, Blue' in their answer field. Make any sense? So,what I need to do is separate the list of answers and say :select member_id from profile_answers whereANY ANSWER in function_to_split(answer) MATCHES ANY OF THESE (selectvalu from dbo.iter_intlist...It seems I might have to join or something, I am just a little lostright now.Here is my proc.ALTER procedure search_detailed_get_ids@question_id as integer,@answers as varchar(8000),@member_ids ntextasdeclare @v as varchar(8000)--get the delimited string of all possible answersset @v = (select bind_data from profiles_questions where question_id =@question_id)--prepare it for the function only accepting 1 charset @v = replace(@v, '||', '|')--gimme all members that matchselect member_id from profiles_answers where question_id = @question_idand answer in(select valu from dbo.iter_intlist_to_table(@v, '|') where listpos in(select valu from dbo.iter_intlist_to_table(@answers, ',')))and member_id in (select valu from dbo.iter_intlist_to_table(@member_ids, ','))returngo
View 3 Replies View RelatedUsers can approach their userprofile on my site using: www.mysite.com/name=peterName is a unique value within my database (db type: nvarchar(50))Now, I have created a clustered index on the username column.However, IMHO its faster to create a clustered index on the (also unique) usercode column since that is of type int.BUT since a user can approach my site based on username I feel that I HAVE to live with this setback in performance....Is that true or is there a better way to solve this issue?
View 1 Replies View RelatedHi, I want to add a newline in a content of sql column (using t-sql and not asp.net textbox) so when content is being rendered in a .net textbox I get separate rows, so insteadcol1 col2 col3 I woule like to havecol1col2col3Thanks
View 3 Replies View RelatedAssuming that I have a table similar to this simplified version:
CREATE TABLE [SomeTable] ([Field1] NVARCHAR(50));
How could I count and rank the instances of specific values in the "field1" column? For example, let's say that I want to show the frequency of the column values like so:
field1 count
some value 24
another word 20
a value 5
(and so on...)
How can I do this?
- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/
Is it ok/possible to use and * in a column that is NVARCHAR type?
View 4 Replies View RelatedHello,This is a simple question, hopefully with a simple answer. I havean nvarchar column of length 255. In one of the rows I have thefollowing sentance - 'See the brown ball bounce'. Is it possible touse a command to remove all of the spaces in that sentance, so thatthe sentance reads 'Seethebrownballbounce'? As you can see, I am notjust interested in getting rid of the trailing and leading spaces.Thanks,Billy
View 1 Replies View RelatedHello,
I have a column that is a currently set as nvarchar(50) and is called DateEmployed.
There are over a hundred rows that contain dates which is in nvarchar format.
This column now needs to be changed to a DateTime datatype. (Don't ask me it was not set
to a dateTime when this was first designed - I wasn't here)
However, I have to change this column to a DateTime without destroying the data.
Is there any easy way to write some script or use studio management to change this.
Currently the data is displayed like this in this column dd/MM/yyyy i.e. 25/8/2007.
The method I am using to try and change this is by going to studio management clicking
modify on the column and changing the datatype from a nvarchar(50) to a DateTime.
I get this following error message:
- Unable to modify table.
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.
Any suggestions would be most grateful,
Thanks,
Steve
I have a table containing records of criminal convictions. There are over 1M records and the only change is additions to the table on a monthly basis. The two columns I need to deal with are convicted.NAME and convicted.DOB
I have a second table that has 2 columns. One is the name of the defendant and the other is the birth date. This would be monitor.NAME and monitor.DOB
There are no primary keys or any other way to join the tables for this search I want to do.
I would like to be able to put a name in the "monitor" table and run a query to see if there is a match in the convicted table.
The problem I am having is middle initials or names. If I want to monitor.name = 'SMITH JOHN' it will return the results fine. The problem I am having is if the conviction is in the database as 'SMITH JOHN T', or 'SMITH JOHN THOMAS'.
How can I use the monitor table with a 'LASTNAME FIRSTNAME' and return results if the convicted table has a middle initial. I tried with a JOIN:
select distinct convicted.*
from convicted
join monitor
on monitor.name like convicted.defendant
and monitor.birthdate = convicted.dob
In a stored procedure, the following code is causing a huge read and CPU load when it really shouldn't. The @IDParameter below is coming in as a parameter to the proc.
Here's the snippet of code where the problem is coming in:
DECLARE @ID INT;
SET @ID = (SELECT ID From OtherTable WHERE FKID = @IDParameter);
SELECT COUNT(*)
FROM LargeTable
WHERE MostlyZeroID = @ID AND MostlyZeroID > 0Most (90+%) of the MostlyZeroID rows are 0 (hence the name) but regardless of distribution this should evaluate with minimal work on SQL Server's part to 0. However, when this was run, it is using a ton of CPU and doing a ton of Reads as it seeks through the entire index. When I look at the execution plan, I see under the seek predicate a Scalar Operator(CONVERT_IMPLICIT(int,[@1],0)) which is what is destroying the performance.
I've confirmed that the MostlyZeroID column in the LargeTable is defined as an INT NOT NULL. I also tested the scenario outside the stored procedure without any variables as the following to make sure it wasn't some kind of strange parameter sniffing scenario:
SELECT COUNT(*)
FROM LargeTable
WHERE MostlyZeroID = 0 AND MostlyZeroID > 0
However, this query also did the implicit conversion. I then tried this out on a temp table populated with a similar number of records (100 million) with a similar distribution and I didn't get the implicit conversion (I got a constant scan as I would've expected) when I did this:
SELECT COUNT(*)
FROM #TestTable
WHERE MostlyZero = 0 AND MostlyZero > 0
I also tried the same on several other tables that are set up similarly (large amount of zeros in an INT column) and I always got a constant scan and didn't do an implicit conversion.
why the query engine is interpreting this 0 as something other than an INT and doing an implicit conversion when getting the count in the scenario above? What can be done to protect against it? In the above scenario, an IF @ID > 0 statement was placed before the code including the count since there was no reason to even run the code if the @ID was equal to zero.
I have a scenario to compare previous records based on each ID columns. For each ID, there would be few records, I have a column called "compare", We have to compare all Compare 1 records with Compare 0 Records. If Dt is lesser or equal to comparing DT, then show 0. Else 1
We always only one Compare 0 records in my table, so all compare 1 columns will compare with only one row per ID
My tables look like
Declare @tab1 table (ID Varchar(3), Dt Date, Compare Int)
Insert Into @tab1 values ('101','2015-07-01',0)
Insert Into @tab1 values ('101','2015-07-02',1)
Insert Into @tab1 values ('101','2015-07-03',1)
Insert Into @tab1 values ('101','2015-07-01',1)
Insert Into @tab1 values ('101','2015-06-30',1)
Insert Into @tab1 values ('102','2015-07-01',0)
Insert Into @tab1 values ('102','2015-07-02',1)
Insert Into @tab1 values ('102','2015-07-01',1)
select * from @tab1
1.) In the above scenario for ID = '101', we have 5 records, first record has Compare value 0, which mean all other 4 records need to compare with this record only
2.) If Compare 1 record's Dt is less or equal to Compare 0's DT, then show 0 in next column
3.) If Compare 1 record's Dt is greater than Compare 0's DT, then show 1 in next column
My expected result set should be like ....
The source column is varchar(1) but i need this as nvarchar(1).. How can I cast this as NVARCHAR...
View 5 Replies View RelatedI am reading data from another data source and storing it in the sqlce database. Some of the string values I'm trying to insert into the database have single quotes in the string (i.e. Johnny's Company). When I try to insert the values with the single quotes, it throws an exception. The code I use to insert the records is as follows:
cmd.CommandText = "INSERT sy_company " +
" (company_id, company, co_name, companyid) " +
"VALUES(" +
"'" + dtSYCompany.Rows[x]["company_id"] + "'," +
"N'" + dtSYCompany.Rows[x]["company"] + "'," +
"N'" + dtSYCompany.Rows[x]["co_name"] + "'," +
"'" + dtSYCompany.Rows[x]["companyid"] + "')";
cmd.ExecuteNonQuery();
When the company name (co_name) has a single quote in it, I get the error. How do I write the insert statement so it will work even though the value being inserted into co_name has a single quote in it?
Thanks so much!
A SqlServer 2005 db has a table with an nvarchar(max) column containing text with paragraph format. When displayed in a Windows form textbox each paragraph exhibits CRLF. When the table is opened in Management Studio the paragraph text is separated with two boxes at each CRLF.
I would like to insert an additional CRLF (or whatever is required) so that when viewed in a textbox each paragraph has a blank line separating it from the next paragraph. Much easier to read!!!
Can this be done? If so, how? Thanks in advance for any help you can provide.
helo..
I want to save file it size about 200MB to the database and after that
I want to get it from the database, the colmun type that i want to
save the file to it is NVARCHAR(Max).
I am using MS sql server 2005.
I was using this code to do this with Image Or Ntext column type but
it dose not working with NVARCHAR(Max) column?
Function SaveFileToDB(ByVal FileName As String, ByVal TblName As String, ByVal FldName As String, ByVal ColumnIDName As String, ByVal RowID As String) As Boolean
Try
Dim addEmp As SqlClient.SqlCommand = New SqlClient.SqlCommand("SELECT @Pointer = TEXTPTR(" & FldName & ") FROM " & TblName & " WHERE " & ColumnIDName & " = '" & RowID & "'", MyConnection)
Dim trParm As SqlClient.SqlParameter = addEmp.Parameters.Add("@Pointer", SqlDbType.Binary, 16)
trParm.Direction = ParameterDirection.Output
addEmp.ExecuteNonQuery()
'''''''''''''''''''''''''''''''''''''
Dim bufferLen As Integer = 1048576
Dim appendToPhoto As SqlClient.SqlCommand = New SqlClient.SqlCommand("UPDATETEXT " & TblName & "." & FldName & " @Pointer @Offset 0 @Bytes", MyConnection)
Dim ptrParm As SqlClient.SqlParameter = appendToPhoto.Parameters.Add("@Pointer", SqlDbType.Binary, 16)
ptrParm.Value = trParm.Value
Dim photoParm As SqlClient.SqlParameter = appendToPhoto.Parameters.Add("@Bytes", SqlDbType.Image, bufferLen)
Dim offsetParm As SqlClient.SqlParameter = appendToPhoto.Parameters.Add("@Offset", SqlDbType.Int)
offsetParm.Value = 0
Dim fs As IO.FileStream = New IO.FileStream(FileName, IO.FileMode.Open, IO.FileAccess.Read)
Dim br As IO.BinaryReader = New IO.BinaryReader(fs)
Dim buffer() As Byte = br.ReadBytes(bufferLen)
Dim offset_ctr As Long = 0
Do While buffer.Length > 0
photoParm.Value = buffer
appendToPhoto.ExecuteNonQuery()
offset_ctr += bufferLen
offsetParm.Value = offset_ctr
buffer = br.ReadBytes(bufferLen)
My.Application.DoEvents()
Loop
br.Close()
fs.Close()
Return True
Catch ex As Exception
MyErrStr = ex.Message
Return False
End Try
End Function
Public Function ReadFileFromDB(ByVal MyCommandText As String, ByVal FileColumnNumber As Integer, ByVal DSTFileName As String) As Boolean
Try
Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand(MyCommandText, MyConnection)
Dim stream As IO.FileStream
Dim writer As IO.BinaryWriter
Dim bufferSize As Integer = 1048576
Dim outByte(bufferSize - 1) As Byte
Dim retval As Long
Dim startIndex As Long = 0
Dim reader As SqlClient.SqlDataReader = command.ExecuteReader()
reader.Read()
stream = New IO.FileStream(DSTFileName, IO.FileMode.OpenOrCreate, IO.FileAccess.Write)
writer = New IO.BinaryWriter(stream)
startIndex = 0
retval = reader.GetBytes(FileColumnNumber, startIndex, outByte, 0, bufferSize)
Do While retval = bufferSize
writer.Write(outByte)
writer.Flush()
startIndex += bufferSize
retval = reader.GetBytes(FileColumnNumber, startIndex, outByte, 0, bufferSize)
My.Application.DoEvents()
Loop
writer.Write(outByte, 0, retval - 1)
writer.Flush()
writer.Close()
stream.Close()
reader.Close()
Return True
Catch ex As Exception
MyErrStr = ex.Message
Return False
End Try
End Function
I need to always have a formatting of 999,999,999.00 in a column called PropertyMap9.
PropertyMap9 is always a nvarchar(max)
How do I SET a value using a CONVERT or CAST to accomplish this ?
Why does this give me a syntax error ? The PropertyMap7 set actually works and performs the calculation and sets the new value for that row, however the next line gives an error. I need all 3 columns PropertyMap9, 8, and 7 to always have the above formatting, while still maintaining the nvarchar(max) datatype in the column. HELP!
update PropertyMapValues
set PropertyMap7 = CONVERT(NVARCHAR(MAX),CAST(PropertyMap8 AS money) - (CAST(PropertyMap9 AS money)),1)
set PropertyMap9 = CONVERT(NVARCHAR(MAX),CAST(PropertyMap9 AS money))
END
Msg 102, Level 15, State 1, Procedure UpdatePropertyMap7, Line 59
Incorrect syntax near '='.
how to write condition for self table year records, such 2012 name and acctno match with 2013 name and acctno then total, provided below,
create table #tab1 (MasterKey int, AcctNo varchar(12),name varchar(25), SumaofShares numeric, request_dat datetime )
--drop table #tab1
insert into #tab1 values (1000, 100,'Tom', 2500, '10/01/2012')
insert into #tab1 values (1001, 101,'Bat', 1550, '08/11/2012')
insert into #tab1 values (1002, 102,'Kit', 1600, '06/12/2012')
insert into #tab1 values (1003, 103,'Vat', 1750, '04/15/2012')
insert into #tab1 values (1010, 104,'Sim',200, '04/21/2013')
[code]....
i would like to get 4 columns output
how to get sumofshares (#tab1) and TotalOutStanding(#tab2) summ up with these values,
MasterKey (#tab1) and IssueKey (#tab2) are like primary key and foreign key
so the request is
need to calculate, sumofshares (#tab1) and TotalOutStanding(#tab2) as below
1)ShareBenefist = U and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1)
then '2012 and 2013 accts UN Veriverted'
2)ShareBenefist = V and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1)
then '2012 and 2013 accts Veriverted'
3)ShareBenefist = N and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1)
then '2012 and 2013 accts NONVERT'
4)year( request_dat) =2102 and Name and Acctno not match with 2013 account name and acctno (#tab1)
then '2012 last year accounts'
5)year( request_dat) = 2013 and Name and Acctno not match with 2013 account name and acctno (#tab1)
then '2012 This year accounts'
for ex 1) the below accounts in #tab1 has both 2012 and 2013 and acctno same in both years and name is same in both years so it is condired as
insert into #tab1 values (1012, 100,'Tom',800, '08/22/2013')
for ex 2)
insert into #tab1 values (1013, 101,'Bat',550, '09/15/2013')
for ex 4) 2012 records there is not match acctno and name in 2013 recods
insert into #tab1 values (1002, 102,'Kit', 1600, '06/12/2012')
for ex 5) 2013 records there is no match of name and acct no with 2012 records
insert into #tab1 values (1010, 104,'Sim',200, '04/21/2013')
insert into #tab1 values (1014, 100,'Pet',200, '02/21/2013')
insert into #tab1 values (1016, 110,'Sun',800, '03/22/2013')
insert into #tab1 values (1017, 111,'Bet',550, '12/15/2013')
Expected Results (just for format)
AcctTypeDescription,SumofShares, OtotalutStand
'2012 and 2013 accts UN Veriverted',2700,234
'2012 and 2013 accts Veriverted' ,2890,234
'2012 and 2013 accts NONVERT' ,4533,325
'2012 last year accounts' ,2334,567
'2012 This year accounts' ,2222,877
Hi,
i have a table with a nvarchar column,i want to send this column value as unicode content to customer mail box , but when i send it a mail with '?' customer receive , how can i accomplish this?
thanks
Hey folks, the question is fairly simple, unfortunately the answer has proven rather elusive.
Is it possible to declare a variable which would then be used to identify either a column or table in an SQL statement?
Here's a basic idea of what I'd like to do:
DECLARE @myVar AS NVARCHAR(50)
SELECT *
FROM @myVar
or
DECLARE @myVar AS NVARCHAR(50)
SELECT @myVar
FROM MyTable
I'm probably looking for some sort of built in function that will accept an argument here... like COLUMN(@myVar) or something of the like. I just don't know where to look...
We have an address table with a house_num field which is a nvarchar.
Most of house numbers are numbers like 1234, 0989
But some of them have a letter behind it like 678 B, 8909 F, even some like this 123/B
We would like to remove any non-numeric letter for this column.
Is there a way to do it?
What is the best way of using the Full-Text feature on HTML?
I want to only search the text and omit the html tags.
If that involves storing as a different format, can someone tell me the best way of doing that?
I'm very new to sql and especially full-text.
Thanks.