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 RepliesI 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'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] ....
how 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
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
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.
Have couple dbs with dash ( - ) in db name. Works fine. However, when I try to do operations with them in SQL Query analyzer, I keep getting errors, e.g.,
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '-'.
What's up?
Thanks,
Yves
Hello
We are in the process of upgrading our SQL 2K server to a cluster environment. Currently the server is setup as a default instance running on a single box with log shipping as a warm backup
It is required to create an instance name for the new cluster to work. My question is
1) Has anybody tried to have an instance name that dash (-) in it.
Example: \SERVERNAMExxx-xx-xxx
I am told that it is not possible to have a server instance named this way. Would like to know if this naming convention is possible under SQL2K If not what is the suggested workaround
This will save a lot of time in application modifications
Thanks in advance
Gabe J
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 RelatedHi All,
I have been working on Reporting Services for quite some time but not able to find a way to create dash boards.
I hereby request you to tell me a way to do that.
An early reply from you help solve my problem.
Thanks
SPC
I'm trying to construct a query that takes results from a table (zip codes, essentially) and strips out the results so that it's just the first 4 digits (rather than the first 4 digits + '-.....'). Which function would allow me to do this in management studio?
View 3 Replies View RelatedI have the following table with two columns:
SEQ_NUM STATUS_CODE
101 D12
102 DWI - REGULAR DWW - EXPIRED
103 WRW - REG WRT - FUTURE
I would like to output the following:
SEQ_NUM STATUS_CODE
101 D12
102 DWI
102 DWW
103 WRW
103 WRT
The Status code column in the source table will have a single 3 DIGIT alphanumeric code, and sometimes a longer string. I need a code that will automatically grab the 3 digit code before the dash.
I want to replace special characters and space except '-' Tried with Pathindex and function but not getting expected results.
Ex: Input kjkdjfdf-234#kei$ ewiw
output:Â kjkdjfdf-234keiewiw
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?
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());
I need to create a linked server to a SQL 2005 server (B) from another SQL 2005 server (A). Problem is that Server B's name has a dash in it, and SQL Server doesn't like dashes in the name when running a query against the linked server. Server B's network name is Server-B. For example, from Mgt Studio on Server A, I've created a link to Server B whose name is Server-B, so in my query from Server A, I issue: SELECT * FROM Server-B, and get the following error message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Any ideas,
Thnx
Roz
I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.
I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT)
The sql task rowset is set to NONE. It is a OLEB connection.
I have two parameters mapped:
tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO.
returnvalue output long 1
I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.
The first part of the sp is below and I set the value @i and return.
CREATE procedure [dbo].[Marketing_extract_history_load_TEST]
@table_name varchar(200),
@i int output
as
Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.
This is my function, it returns SQLDataReader to DATALIST control. How
to return page number with the SQLDataReader set ? sql server 2005,
asp.net 2.0
Function get_all_events() As SqlDataReader
Dim myConnection As New
SqlConnection(ConfigurationManager.AppSettings("..........."))
Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2)
parameterState.Value = Request.Params("State")
myCommand.Parameters.Add(parameterState)
Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4)
parameterPagesize.Value = 20
myCommand.Parameters.Add(parameterPagesize)
Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4)
parameterPagenum.Value = pn1.SelectedPage
myCommand.Parameters.Add(parameterPagenum)
Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4)
parameterPageCount.Direction = ParameterDirection.ReturnValue
myCommand.Parameters.Add(parameterPageCount)
myConnection.Open()
'myCommand.ExecuteReader(CommandBehavior.CloseConnection)
'pages = CType(myCommand.Parameters("@pagecount").Value, Integer)
Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
End Function
Variable Pages is global integer.
This is what i am calling
DataList1.DataSource = get_all_events()
DataList1.DataBind()
How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck
I'm a Reporting Services New-Be.
I'm trying to create a report that's based on a SQL-2005 Stored Procedure.
I added the Report Designer, a Report dataset ( based on a shared datasource).
When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.
I'll only reproduce one instance of the error for the sake of brevity.
[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.
I've searched on this error and it looks like it's a Visual Basic error :
http://msdn2.microsoft.com/en-us/library/8x403818(VS.80).aspx
My guess is that BIDS is creating some VB code behind the scenes for the report.
I can't find any other information that seems to fit this error.
The other reports in the BIDS project built successfully before I added this report, so it must be something specific to the report.
BTW, the Stored Procedure this report is based on a global temp table. The other reports do not use temp tables.
Can anyone help ?
Thanks,
I am trying to bring my stored proc's into the 21st century with try catch and the output clause. In the past I have returned info like the new timestamp, the new identity (if an insert sproc), username with output params and a return value as well. I have checked if error is a concurrency violation(I check if @@rowcount is 0 and if so my return value is a special number.)
I have used the old goto method for trapping errors committing or rolling back the transaction.
Now I want to use the try,catch with transactions. This is easy enough but how do I do what I had done before?
I get an error returning the new timestamp in the Output clause (tstamp is my timestamp field -- so I am using inserted.tstamp).
Plus how do I check for concerrency error. Is it the same as before and if so would the check of @@rowcount be in the catch section?
So how to return timestamp and a return value and how to check for concurrency all in the try/catch.
by the way I read that you could not return an identity in the output clause but I had no problem.
Thanks for help on this
SM haig
hi, good day,
i have using BCP to output SP return data into txt file, however, when it return nothing , it give SQLException like "no rows affected" , i have try to find out the solution , which include put "SET NOCOUNT ON" command before select statement, but it doesn't help :(
anyone know how to handle the problem when SP return no data ?
thanks in advance
I have a stored procedure that selects the unique Name of an item from one table.Â
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
How can I code a stored procedure to do this?
Hi,
Anytime I BCP data from a table containing characters like ö it gets muddled up when I look at it in the text file. The code page of the NT OS is 850 and the character is included in it. Why is the character changed during the BCP ?
Bob
Hello all.
Ok this is going to be a little hard to describe but here goes
I have a table within which one column is used to store a SQL select statement as a string. This means that the whole select statement has to be enclosed within two ' characters. The problem is that the SQL statement itself contains these characters. The specific SQL statement i wish to save as a string is:
select
'<A HREF=DisplayOnlyDiscipline?SESSION_ID=' + :SESSION_ID +
'&DISP_REF=' + cast(DISP_REF as varchar) +
'&EDIT_REF=' + :EDIT_REF + '>' + cast(DISP_REF as varchar) + '</A>' as Reference,
V.DESCRIPTION as Discipline_Stage,
DISP_DATE as Date
from
DISCPLIN D
left outer join
V_DISP V on (V.CODE = D.DISP_CODE)
where
EMPLOY_REF = :EDIT_REF
order by
DISP_DATE DESC
Can anyone suggest how i get round this?
thanks for reading peeps
Pls, what d this query return if applied on char type (string type)
select * from myTable where myNameColumn Between 'B' AND 'E'
would return all the names that start with : B, C, D, E
like these:
Bob, Chris, Edward, David
but not :
Marry, Jean....
or how does it work exactly when applied on string type but only putting one character in the search.
Thanks.
How can i add character 0 to the database??
i have a string of characters and one of them is 0 (its a license file)
i would like to save it to the database. but i get unclosed character string.
Hope its clear.
Tks
its a new database so you can also tellme wich datatype i need.
what nchar() value equals the tab character. I need to replace it in astring.
View 3 Replies View RelatedI'm using MS SQL intelligence studio's SSIS import wizard to import some Excel files into SQL tables and for certain fields I'm getting this strange character instead of NULL. Does anyone know what this character is, how to avoid it, or a SQL query that will get rid of it? I can't even copy it to my clipboard.
View 12 Replies View Related
Hi
I have got a column having data like
RT 12.5R20 (P) OL
RT 12.5R244(SP)GRADER
I want only need the value before '('. So, the result will be RT 12.5R20 and RT 12.5R244
Since the length of the character will change. Is there any funtions in T-SQL that I can use to achieve this?
Thanks