Hi there,
Can anybody tell me how the character of hard return expressed in SQL Server. For example if I wanna convert any hard return character in a varchar field to something else, like a space, how could I write Replace function? Or if I wanna search any hard return character, how could I write where clause?
thanks a lot
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,
Does anyone know if I can strip out a carriage return in a varchar column. So far the only way I feel that this could be done is to write VB Script in DTS.. Could anyone get me started on the VB script? IF so reply or email me . Thanks and this could be a billable call.
I will pay for this info.
4046323231 cell
I have a data set that returns the following
Customer
pncount
pn%
monthval
ABC
5
30
01/01/2007
DEF
9
20
01/01/2007
GHI
4
40
01/01/2007
ABC
DEF
4
90
02/01/2007
GHI
7
10
02/01/2007
ABC
4
5
03/01/2007
DEF
GHI
6
100
03/01/2007
but I need this
01/01/2007
01/01/2007
02/01/2007
02/01/2007
03/01/2007
03/01/2007
abc
5
30
4
5
def
9
20
4
90
ghi
4
40
7
10
6
100
Any Idea Help
I'm having a hard time to getting back an xml data back from a stored procedure executed by an Execute SQL task.
I'm passing in an XML data as a parameter and getting back resulting XML data as a parameter. The Execute SQL task is using ADO connection to do this job. The two parameters(in/out) are type of "string" and mapped as string.
When I execute the task, I get the following error message.
[Execute SQL Task] Error: Executing the query "dbo.PromissorPLEDataUpload" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@LogXML"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I also tried mapping the parameter as XML type, but that didn't work either.
If anyone knows what's going on or how to fix this problem please let me know. All I want to do is save returning XML data in the parameter to a local package variable.
Thanks
I want to return the character(s) in a string directly after a dash (-) character. For example in the following numbers 72187-1 & 72187-2 I am interested in returning 1 & 2 respectively.
It is possible in my scenario that the number after the dash is two digits long, e.g.: 72187-12 in which case I want to return 12.
I want to return the character(s) in a string directly after a dash (-) character. For example in the following numbers 72187-1 & 72187-2 I am interested in returning 1 & 2 respectively.It is possible in my scenario that the number after the dash is two digits long, e.g.: 72187-12 in which case I want to return 12.
View 6 Replies View Relatedhow can i return text in a column up until a special character?
I have this:
abcd_1234
abcdef_123456
And i want this:
abcd
abcdef
How can i return everthing thats left (or right) of the '_' character? i have tried use LEFT/RIGHT and SUBSTRING but i just cant make it to work.
thx in advance //Mr
Is their a way to return only a portion of a string returned from a query? Please look at the following query and what it Yields:
select UserName from MyDirectory
NTSRJoe MeekletonNTSRAnfreesa ClinklHeadBRYAmy KooplosaAKVermian Strikoos
How can I fix this query so that it returns everything to the right of the '' character?
So I want the query to instead return the following:
Joe MeekletonAnfreesa ClinklHeadAmy KooplosaVermian Strikoos
Hi I have a text file which I need to import into Access or SQL.
It is 500,000 records which is pipe delimited. The problem is, is that it can contain carriage returns: (square symbol).
Therefore I need to find and replace these characters
Does anybody know of any free ware text file viewers that can do this??
Best Regareds
David
I had to import some FileMaker Data into SQL Server 2005 and in the resume field a "SQUARE" special character is appearing everyplace that a return should be.
It has really messed up the formatting. I know how to use the updatetext in a cursor to replace the special character but I don't know what to replace the "SQUARE" special character with so the text is displaying a new line rather then this "SQUARE" character.
Any help would be appreicated.
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 RelatedIt's because of this one or maybe more people that the whole world of the internet becomes so much of crappy messy place... *sighs*
Since there is really no specifics on deleting a topic, this is the best I could do.
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
How can I search for all the O'Connor's in my SQL Server database?eg: SELECT [Surname] ,[FirstName] FROM [myDB].[dbo].[Names] WHERE ([Surname] = 'O'Connor')
View 5 Replies View RelatedHi I have a table in my database and one of the columns is called 'Month'. Here only one month is inserted according to what the user selects from a dro down list.
WHat i want to be able to do is find the average of the column 'ScoreOut' on a month by month basis. My code works fine if th database contains at least one row with every month of the year represented. I run into problems when one of the months is not represented. here is my code:string month = "February";
SqlConnection myConn = new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True");String selectCommand = "Select AVG(CAST(ScoreOut as float)) from tblRound where Name = '" + lblName.Text + "' AND Month = '" + month + "'";
SqlCommand myCmd = new SqlCommand(selectCommand, myConn);SqlDataAdapter da = new SqlDataAdapter(myCmd);DataTable dt = new DataTable();
da.Fill(dt);
myConn.Open();
myCmd.ExecuteNonQuery();string info = String.Format("{0:0.00}", Convert.ToDecimal(myCmd.ExecuteScalar()));
myConn.Close();
lblHole2.Text = info;
Does anyone know how to get around this problem. If a month is not represented, i would like the label lblHole2.text to be equal to 0.
thanks
hi,
i'm wondering if there's a possibility for me to insert values into a table without knowing fix number of fields? in other words, i won't know how many fields i have in a single table until users enter data.
any suggestions or recommendations are welcome. thanks in advance.
Hello,
I have a global report which contains many countries, and I want a specific user (let's say French) to access only to a specific country (France).
So far in SSAS, I have created a role, and assigned my user to this role. When I browse the cube with the new role, I see only the information regarding the country France.
However when the user access to the report through the web interface he can see all the countries. Moreover when I browse the cube with the user (DomainUserName) which belong to the role, I can see all the countries.
So I think I have missed something about role security and the link SSAS-SSRS...
Do you have ideas about how to deal with my problem?
Any help is welcome...
Thanks,
Guillaume
Hi,
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!!
First we need to run all packages for initial loading and then we need to run all packages every night.
But inside all packages we need to mark some records as invalid data.
How to deal with all these invalid data after data migration?
If I reject to transfer these data, I will get reference error during next data migration loading.
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.
Hi please could someone give me a explination of how SQLserver deals with multiple databases, We have multiple website that are all run from the same server all from SQLserver. Now we have a couple of databases which are very busy and have a lot of people constantly accessing them and then we have quite a few with much less traffic.
How would these databases effect each other? Would the busy database, mean that the many small databases will all be slowed dramtically?
Everything is okay at the moment but we are considering putting another fairly large database on the server and am worried about it having a sever effect on the smaller databases. Cheers Ed
Try hard to become familiar with T-SQL.Can anybodey tell me the best way to deal with set's provided by astored procedure. Til yesterday I thougt trapping set in temp tableusing INSERT EXEC is a way out of this, but then I struggeled withnested INSERT EXEC's.What are all the system proc's good for if the results cannot beevaluated? The approach of modular programming is to have code doingsimilar things in one place.If I try to make use of sp_helprolemember to get login names for moreroles, pack the logins in one table and return the result set in a SP,the procedure which calls that is unable to evaluate the set.On the other hand I read the advice, not to access system tablesdirectly.Is there a way out?
View 9 Replies View RelatedHi, all experts here,
Thanks for your kind attention.
I have a question on missing_value substitution. As we know in the algorithm parametres setting, there is an option for us to select the method to deal with the missing_value: None, Previous, or Mean. My question is: to numeric data column, we can select Mean method to deal with its missing value. But how about discrete type of data column? What is the best practice to deal with this kind of data? I do think it to a great extent matters the quality of the models being trained?
Can please any of you guys here give me any advices and suggestions on this based on what best practices you have got?
I am looking forward to hearing from you shortly and thanks a lot in advance.
With best regards,
Yours sincerely,
I have 12 colums named in source
ERR1 - ERR12. I do have a reference table for ERR1 - ERR12 and haing values 1-12
Now the nature of ERR columns is that each of them has either 1 or more than 1 TICK mark in a small BOX.
In the destinaltion table I have only 1 column to get the Values from 1-12.
How do i match and get the values.
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?
when I backup the SQL Server 2005 database ,always get prompt
Error description: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'E'.
+48166A04.0004 [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
+48166A04.0004 [Microsoft][ODBC SQL Server Driver][SQL Server]The label 'E' has already been declared. Label names must be unique within a query batch or stored procedure.}
how to deal with it, thanks
I can get following data information from table1Name DescriptionA AviationA01 Aviation GeneralCA01 CapitalCA01-006 NEW CTB SignageA02 CapitalCA05-005 East End RoadwayCA05-006 Modify RoadHow do I write stored procedure to get following results based on the data from table1Code Name Description Level 1 A Aviation 1 1.01 A01 Aviation General 2 1.01.01 CA01 Capital 3 1.01.01.006 CA01-006 NEW CTB Signage 4 1.02 A02 Capital 2 1.02.01 CA05-005 East End Roadway 3 1.02.01.006 CA05-006 Modify Road 4Thank a lot!
View 1 Replies View RelatedHi, all here,
I currently have a question about unknown member in cubes (as we know, there is always an additional member called unknown in an OLAP dimension)for data mining. I mean is there any way for us to deal with unknown member in cubes when mining cubes?
Thanks a lot in advance for any guidance and help.
First a bit of background.
We use the Windows-logon to authenticate en identify the user. With this logon we check within most of our reports what a user is allowed to see.
Depending his level or geographical location he is allowed to run reports on one to all sites. He can selects the sites from a Multiple Select box. This works fine.
Now, we want to use data-driven subscriptions, I understand why we can't use this parameter, but how do you people deal with this kind of configurations.
I would prefer not to 'double' the reports in a normal and a subsciption version .....
I try to convert a Procedure that join 8 tables with INNER AND OUTER JOIN, my understanding is that the Lookup task is the one to use and I should break these joins into smaller block, it takes a long time to load when I do this, since each of these tables had 10-40mill. rows and I have 8 tables to go thru, currently this Stored Procedure took 3-4min to run, by converting this to 8 Lookup tasks, it ran for 20min. has anyone run into this issue before and know a work around for this.
Thanks
Hi,
Does anyone know how to deal with image data type within stored procedure when using SQL2000?
I have a table and there is an image data type column. In this table I need to make a copy of one row within a table through a SP /it means to retrieve the whole row within SP, change another columns data (but, that the image is not modified) / and save the modified row back to the same table.
Problem is, that within SP is not alowed to use local varaibles of image data type. Does anyone know a solution for this? Please.
Thank you.
I have added a page refresh in combination with 'Command Notification' to ensure that my data is fresh,( it is updated on the sql server database approx. every 5 mins).
However, I also allow filtering on the table contents and every time I refresh the filtering is lost. Is it possible to maintain the filtering through the refresh?
<meta http-equiv="refresh" content="30"/>..<asp:SqlDataSource ID="SqlDataSource1" runat="server" ..SelectCommand="SELECT ... EnableCaching="true" CacheDuration="Infinite" CacheExpirationPolicy="Absolute" SqlCacheDependency="CommandNotification" >