I was wondering what everyone felt about the fomats in character
fields where the front end application accepts anything.
I wouldn't want a customer table where the customer name was lower
case on one, upper on another and who knows on the third.
If character fields are not consistent, then formatting will have to
happen every time someone access the data for reporting - as an
example ...
Thanks,
Craig
Hi,another problem I have is that have compounded fields in my sql table.Exampleproduct@customerI need a simple function to return "customer", so it should return the valueafter "@", unfortunate "@" will sometimes be character number 6, sometimescharacter number 7 etc.regardsJorgen
View 1 Replies View RelatedI know there has to be a way to do this, but I've gone brain dead. Thescenario..a varchar field in a table contains a date range (i.e. June 1,2004 - June 15, 2004 or September 1, 2004 - September 30, 2004 or...). Theusers have decided thats a bad way to do this (!) so they want to split thatfield into two new fields. Everything before the space/dash ( -) goes intoa 'FromDate' field, everything after the dash/space goes into the 'ToDate'field. I've played around with STRING commands, but haven't stumbled on ityet. Any help at all would be appreciated! DTS?
View 1 Replies View RelatedIn a SQL Server Db, what is the maximum amount of data a field can hold?
I am using right now nvarchar with a length of 4000.
Is there another setting to allow even more in a field?
Thanks all,
Zath
Does ORDER BY work on character data type in SQL Server through ODBC?
I tried using the SQL Query Tool in SQL Enterprise Manager and it works but using through ODBC I can't get any results.
Query: SELECT company_id, company_name FROM lt_company ORDER BY company_name
company_id = integer
company_name = 30 characters
Hello,I wonder how I can insert a string which contains #, as # is a specialcharacters in sqlThanks
View 1 Replies View RelatedHiI have a character field (char ot varchar) that I want to force only tocontain numeric characters.Can that be done by way of defining a constraint on the field ?or by any other way in the field/table definition ?What id the syntax ?Anyone have examples ?ThanksDavid Greenberg
View 1 Replies View Related
here is my business/data object for some reason I only get the first character back, say value is Charlie, I only get C public string GetUserName(long UserId)
{
try
{
string userName;
DiscussionDB data = new DiscussionDB();
List paramlist = new List();
paramlist.Add(data.CreateParameter("@UserId", UserId));
paramlist.Add(data.CreateParameter("@UserName", "", ParameterDirection.Output, DbType.String, 20));
data.ExecuteNonQuery("dbo.Discussion_User_Name", ref paramlist);
userName = paramlist[1].Value.ToString();
return userName;
}
catch
{
throw;
}
}
ALTER PROCEDURE [dbo].[Discussion_User_Name]
@UserId bigint,
@UserName varchar(20) output
AS
SET NOCOUNT ON
SET @UserName = (Select [Name] from Discussion_Member WHERE UserID = @UserID)
if (@UserName is null or @UserName = '')
BEGIN
SET @UserName = (Select UserName from Membership_User WHERE UserID = @UserID)
END
I have a 6 char field which has to be converted to a datetime. I thought I had it solved when I did this
convert(datetime,(left(dob,2)+'-'+substring(dob,3,2)+'-'+right(dob,2)))
Problem is with a date value of 081649
I get 08/16/2049 instead of 1949, where did I goof
Hi,
I am getting a date string that is contained in a character field (char(20)).
An example of the data is as follows:
2005-09-20121315001
it is in the format YYYY-MM-DDHHMMSSMMM.
I want to insert this value into a datetime field. I have used convert, but can only insert it when I split out the time portion and separate them with a colon
i.e. to get it to insert into a datetime field the data has to be in the following format
2005-09-20 13:15:18.001
YYYY-MM-DD HH:MM:SS.MMM
Is there a style parameter in the convert function that will allow me to insert this value without having to separate the hours/minutes/seconds with a colon? I have tried BOL but it seems that all the examples have colons in them where there is a time portion to the date,
Thanks in advance
Here's a question for the SQL gurus out there:
I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be
8060
8060,4450
8060,4123,3245
Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example
CODE1=8060, CODE2=4450, CODE3=NULL.
I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field?
Any help very greatly appreciated.
Thanks, Simon.
I have one table and this field is character field with save data in below.
Bonus_table->bonus_amt_field. Char(20)
======================================
Record information
0
Null
Blank
3
4
Null
Blank
if i want to convert this character field => change to numeric field to display ,how to handle "Blank" and "null" record?
The result expect:
0
0
0
3
4
0
0
I try this query but wrong message :
select cast(convert(numeric,3)bonus_amt) as bonus_amt
from test
Hi, I need to replace a character from a column of a txt file. I have defined the column and the values (datetime type for sql server) that I receive are like this: 2008-02-15-20.07.19
So, I need to replace the "." with a ":" beacuse those are minutes and sql server uses ":" for minutes
How can I do this? Any help?
Thanks
I need to update the string in a longtext field. NewString = "This is a 'test'"
I can't figure out how to use an escape character to identify the string with " ' " and then also include the " ' " character inside the string.
Can anybody help me?
Thanks,
Alex
How to modify values in a SQL Database. In the table PRDDEmpmaster there is a field called email_address, and for those fields that have a value I need to change that value to add { } opening/closing "curly braces" to that field.
For example,
RPARSONS@YAHOO.COM becomes
{RPARSONS@YAHOO.COM} . Nulls/empty fields remain as is.
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 am attempting to import a flat file and have come accross and issue that I do not know how to fix in SSIS. The issue is that some of the text fields use quoted identifiers. This is not an issue in itself. The problem is they also use quotes as escape character if quotes are on the field.
So I see instances of "" because inside the quoted field is a quote. How do i specify an escape character?
I am getting a data loaded in a DB table to a character field padded from the right with hexadecimal zeros 0x00. How to get rid of it?
Thank you.
Because of a limitation on a piece of software I'm using I need to take a large varchar field and force a carriage return/linebreak in the returned sql. Allowing for a line size of approximately 50 characters, I thought the approach would be to first find the 'spaces' in the data, so as to not split the line on a real word. achieve.
--===== Simulate a passed parameter
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = (select a_notes
from dbo.notestuff as notes
where a_id = '1')
[Code] .....
I want to insert a value which has the UTF-8 encoding into a field of the database which has the "text" data Type
but it saves like this : " ?????????????? "
I would be thankful if u tell me how should I save it ?
here is the SqlComand I wrote :
string comand = " Insert into Table1 ( title , Body ) values ( ' " + textBox1.text + " ' , ' " + textBox2.text + " ' ) " ;
Table1
=============
title nvarchar(1000)
body text
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
I have the following scenario, The contents of main file are like :
ServerCentral|||||forum|||||||||||||||is||||||the||best
so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it
And I need the output in the following form:
ServerCentral=forum=is=the=best
so=be=on=it
The logic being that multiple and consecutive occurrences of the special character, here - pipe , should be replaced by a single special character.
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] ....
Hi!
I have a table like this below and it doesn't only contain English Names but it also contain Chinese Name.
CREATE TABLE Names
(FirstName NVARCHAR (50),
LastName NVARCHAR (50));
I tried to view the column using SQL Query Analyzer, It didn't display Chinese Character.
I know that SQL Server 2005 is using UCS-2 Encoding and Chinese Character uses Double Byte Character Set (DBCS) Encoding.
I want to read the FirstName and LastName columns and display in Window Form Data Grid and ASP.NET Grid View.
I tried to use this code below and it didn't work. It convert some of the English Name to Chinese Character and it display the chinese character and some still in the original unreadable characters.
Does anybody know how to read those character from SQL Table and display the correct Chinese Character without converting the English Name into Chinese also?
Thanks
int codePage = 950;
StringBuilder message = new StringBuilder();
Encoding targetEncoding = Encoding.GetEncoding(codePage);
byte[] encodedChars= targetEncoding.GetBytes(str);
.
message.AppendLine("Byte representation of '" + str + "' in Code Page '" + codePage + "':");
for (int i = 0; i < encodedChars.Length; i++)
{
message.Append("Byte " + i + ": " + encodedChars);
}
message.AppendLine(" RESULT : " + System.Text.Encoding.Unicode.GetString(encodedChars));
Console.Writeline(message.ToString());
My SQL 7.0 database is set to show UK format date ranges. But when opening a table and adding criteria to the grid pane I having to put the US format date to retrive correct results...
Is this correct. I am wanting to be aboe to enter UK format in the criteria
Thanks...Scott
Hi,
I am creating a VB application that will run on a NT machine with Regional Settings as UK English and dd/mm/yyyy settings. When I try to input these values into SQL 7.0 ( default database setting mdy and us_english with Regional Settings as UK dd/mm/yyyy) , I get the error -" The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value " .
Can I change my databse to always be dmy format. It seems to be active for a connection , can it be configured somewhere .
Any help will be greatly appreciated !
Regards
Anand
Hey guys,
I have a file that has date formatted like so: 2006-11-16 20:12:00
I would like the dateformat to be like mm/dd/yyy hh:mm
The file is being pulled into a varchar field..... as 2006-11-16 20:12:00
when I do a conversion i can only get it to mon 11,2006
any suggestions
I read this and got confused...I use PHP and got this:$sql = "delete from blabla where startdate< =to_date('2000.05.05','YYYY-MM-DD')";$result=odbc_exec($conn, $sql);Result: Warning: odbc_exec() [function.odbc-exec]: SQL error:[Microsoft][ODBC SQL Server Driver][SQL Server]'to_date' is not arecognized function name.to_date should work?At the other end i'd like to read out startdate as a date var or formatit as I need.... (dd.mm.yyyy). Convert makes me confused... helpplease?S
View 3 Replies View RelatedIs it true that binary and varbinary has a max-kapacity at 8000 bytes? Imjyst unsure how to read the dokumentation and want to be completely sure.And image is only one to use for data aobve that size?Thx in regards for clearing things out for meReagardsAnders
View 1 Replies View RelatedHi,
I have two beginner questions:
1. How should I set up report layout/printing format so it would fit to the page?
2. Is there a way to change the color of every other row in the table? Can I highlight the rows based on some kind of a condition?
Thank you.
OK - totally new to this game but here goes ...
I want to put together a database to record times achieved by swimmers in competition. The database itself is simple but I don't know what format to use to store the time achieved. Times can vary, for example 35.27 (SS.00), 1.12.63 (MM.SS.00). Oh, and by the way I want to rank the output but haven't even begun to think about that yet!
Hi,
My ISP says that the file format for SQE .mdf files is different than a regular sql2005 .mdf file. So, what I am told to do is backup my SQE file, and send it to them to restore into a SQL2005 format.
Does that make sense? Or should I simply be able to upload a .mdf file to their SQL2005 server without backing it up for them to restore?
Thanks,
-JS