i have a column name remarks and i want to retrieve data like by deleting some of the leading charaters and some last characters and show the middle one.
like remarks has CALLTRANSFER_OVER_SIP:XfrTime=86.05599975585938_en and i want to show only "XfrTime=86.05599975585938" this much
Lets say I have a column of type varchar and need to extract an integer value from the middle of it. The string looks like this:'this part is always the same' + integer of varying length + 'this part is different but always the same length'Is there a way to trim the constant string lengths from the beginning and end?
View 2 Replies View RelatedIm having a dumb moment... what is the easiest way to get the third and fourth character of a string???
Thanks :)
khosara writes "I have one parameter @String with value "My name is Khosara".
How to get the value only "Is khos".
Could you please help me, witch method shold i use.
Thank in advance."
I need retrieve only strings that hold the sub-string in middle. no first no last just middle.
See: the sub-string is "test"
Yes: test test test
Yes: take test test
Yes: test test take
Yes: take test take
No: test test
No: test take take
No: take take test
No: test
I prepare sample data...
declare @sample table(s varchar(50));
insert @sample values
('test test test'),
('test test'),
('test'),
('test take test'),
('test take take'),
('take test test'),
('take test take');
I am trying to write a query in sql query analyzer that will extract a date that appears after the first comma in the string. An example of the data is below:
DAVE,12/31/03,17:18,PERIODENDP
AS,01/16/06,16:51,CUSTOMERS_VW
EM,11/09/07,15:08,ORDER_ENTRY_
I want to select the 8 characters after the first comma in the field, and then convert it to a date format.
I am a novice, so any help with the correct function and syntax would be greatly appreciated.
thanks,
Dave
I'm writing a query where I have multiple left-outer joins but I keep getting multi-part identifier error. See the query below?
SELECT gl.seg5 Natural
,gl.seg2 Office
,gl.seg3 Dept
,gl.seg4 Team
,gl.seg6 Sub
,gl.seg7 Tkpr
,gl.seg1 Comp
,'CHK' Source
[Code] ....
Errors
Msg 4104, Level 16, State 1, Line 68
The multi-part identifier "csddt.baid" could not be bound.
Msg 4104, Level 16, State 1, Line 68
The multi-part identifier "csddt.cknum" could not be bound.
Msg 4104, Level 16, State 1, Line 68
The multi-part identifier "csddt.ckline" could not be bound.
I usually do this through Access so I'm not too familiar with the string functions in SQL. My question is, how do you remove characters from the middle of a string?
Ex:
String value is 10 characters long.
The string value is X000001250.
The end result should look like, X1250.
I've tried mixing/matching multiple string functions with no success. The only solution I have come up with removes ALL of the zeros, including the tailing zero. The goal is to only remove the consecutive zeroes in the middle of the string.
Hi,In the following query the calculator gives value 3.3 but the query returns 3.0 I need to get the decimal part also. i.e I need to get 3.3 as answer from the query. select cast(66/20 as decimal(6,2)) Need help.Thanks
View 3 Replies View RelatedOk following scenario
create table dbo.ADAssets
(Computername nvarchar(max))
insert into dbo.ADAssets
values
('AIRLBEOF3565 CNF:4e926e06-6f62-4864-aebd-6311543d',
'AIRLBEOF3565')
So if execute the following
select Computername
from dbo.ADAssets
where Computername like
'AIRLBEOF3565%'
I get both records,but if I do this
select *
from dbo.ADAssets
where Computername in
(
'AIRLBEOF3565 CNF:4e926e06-6f62-4864-aebd-6311543d',
'AIRLBEOF3565'
)
I only get AIRLBEOF3565
So the big picture is that I need to compare 2 tables to find records that match & don't but that I get matches that shouldn't be & matches that aren't.
Hello to all,
I have a problem with ms sql query. I hope that somebody can help me.
i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)). Example Datas for Table Relationships: IDMember Relationships .
3387 (2345, 2388,4567,....)
4567 (8990, 7865, 3387...)
i wirte a query to check if there is Relationship between two members.
Query:
Declare @IDM int; Declare @IDO int; Set @IDM = 3387, @IDO = 4567;
select *
from Relationship where (IDMember = @IDM) and ( cast(@ID0 as char(100)) in
(select Relationship .[RelationshipIDs] from Relationship where IDMember = @IDM))
But I get nothing by this query.
Can Someone tell me where is the problem? Thanks
Best Regards
Pinsha
I am trying to find a way to find a certian character in a string and then select everything after that character.
for example i would look for the position of the underscore and then need to return everthing after it so in this case
yes_no
i would return no
What is the logic of the below DELETE statement?It deletes all records where the file_modified column value is smaller then the maximum value in the same column, but not crystal clear how...
delete a
from Staging a
left join (select max(file_modifieddate) as file_modifieddate from Staging) b on
a.file_modifieddate = b.file_modifieddate
where b.file_modifieddate is null
Hi everyone... I have a problem on deleting a record. I accidentally duplicate the record.
I need to delete other records and left only 1 record based on each date and employeenumber.
supposing i have a table hrempshiftschedule
i have 3 fields... empno, shiftdate, and shifttype
sample data...
empno shiftdate shifttype
1 1/1/2008 S1
1 1/1/2008 S1
2 1/1/2008 S2
2 1/1/2008 S2
2 1/1/2008 S2
2 1/2/2008 S2
2 1/2/2008 S2
2 1/3/2008 S3
3 1/1/2008 S3
4 1/4/2008 S4
4 1/5/2008 S5
expected output....
empno shiftdate shifttype
1 1/1/2008 S1
2 1/1/2008 S2
2 1/2/2008 S2
2 1/3/2008 S3
3 1/1/2008 S3
4 1/4/2008 S4
4 1/5/2008 S5
pls help and guide me.... thanks
RON
________________________________________________________________________________________________
"I won't last a day without SQL"
I setup replication (merge) for a test db with 11 tables. Deleted the publication and want to delete the database itself. Sql server says it can't be deleted becuase it's has replication setup! Bug??
View 1 Replies View RelatedI had a publication (merge) setup for a database. Deleted the publication and tried to delete the database. Sql server says it can't be deleted because it's has replication setup. Bug??
Need help..
I need to select from a text field (lastname, firstname) the first part which is the last name. The format is exactly like the parenthesis. Any ideas?
Thanx
select left('Hello World /Ok',charindex('/','Hello World /Ok')-1)Hello WorldThat works fine.However I got an error message:select left('Hello World Ok',charindex('/','Hello World Ok')-1)Instead of:'Hello World Ok'I get:Server: Msg 536, Level 16, State 3, Line 1Invalid length parameter passed to the substring function.Microsoft Doc incorrectly says:"LEFT ( character_expression , integer_expression )integer_expressionIs a positive whole number. If integer_expression is negative, a nullstring is returned."Is there an easier solutoin using left or any other string functioninstead of using a case statement?Also, charindex('/','Hello World Ok') should return NULL instead 0 sothat we can use isnull function.Thanks.
View 2 Replies View RelatedI have a column Sports which contains a string.the string is comma delimited, so may contain e.g. 1,3,2,6,8,19 or 6,22,13 etc.What is (performance wise) the fastest way to select all the rows where the number 2 is in the Sports column.....?(I can't search on ",2," since "2" may also be at the start of the string....)
View 1 Replies View RelatedI am trying to strip off 'XYZ' from column1 in table1 whenever it occurs
Any help appreciated
saad
Hello,
I have a field in my table that includes free form text. Within this text are two five digit numbers seperated by a forward slash (/). I need to be able to locate the "/" and then return and display the numbers before and after the "/" seperately. For example:
"the text looks like this but has two numbers 55555/66666 within the string"
I need to get the "55555" and the "66666" in oprder to then display them. can anyone help? I am using ASP/SQL. Appreciated in advance!
how can you return part of a string and convert it into an integer value? Maybe like this:
convert(left(column, 3) as int)
does that work?
The Yak Village Idiot
Is there a function that will extract part of a string when the data youwant does not occur in a specific position?Field "REF" is varchar(80) and contains an email subject line and the emailrecipients contact nameExample data:Rec_ID REF1 Here is the information you requested (oc:JohmSmith)2 Thanks for attending our seminar (oc:Peggy SueJohnson)3 Re: Our meeting yesterday (oc:Donald A. Duck)What I need to extract is the contact name that is in parenthesis after theoc:The name is always in parenthesis and occurs immediately after "oc:" - nospaces after the "oc:"Thanks.
View 4 Replies View RelatedHi,
I have 2 questions.
1. I have a table with a column for region names. Region Names are in 2 formats basically - "NAME-BU*RM" OR "NAME*RM".
I want to extract just "Name" from this string.
The length of "Name" varies and I want to extract all characters included for "Name".
Can anyone advise what the query/SQL statement would look like?
2. I wrote a VB code to generate a xls file. Users are able to run it fine but if they have another file with same name already open, then it just crashes excel.
So I want to include a code that checks if file "file.xls" is open on user's machine.
If file is open, then message "file "File.xls" is already open. Generating File_1.xls"
Run the code but create the file with file name "file_1.xls"
If file doesn't exist, then run code and create file with file name "File.xls"
Please advise.
I'm trying to form a query that will select part of a result.I'm trying to take out the ending of results that end in '-PR'.
Example: The original result is 'Jbbx32-PR'. I want to select it as 'Jbbx32'.
I'm passing a variable to SQL and I want it to query a column (IP_user), but query any part of what is given. For example if I given Chris it would bring up Chris, Christian, Christine, etc. What is the syntax to do this?? thanks
View 7 Replies View Relatedhi, I have a field named city_state that contains city and state together (Warren, OH) in the same field. I need to create two seperate columns one city, another state. how can I extract the state out of the city_state column and have two different column.
thanks
Ahmed
I am trying to make a query that will group my errors messages together - my problem is that each of the error messages is unique, due to them having an unique id in them.
"GROUP BY LIKE '%ThePartToGroupBy%'"
Here is a sample order # we used for one of our shipments: BL-53151-24954-1-0001-33934
I need to extract the "24954" portion of that order # while within an INNER JOIN, but not sure how.
My problem is we have 2 order tables: OrderTable1 contains a field with the full order #. OrderTable2 contains a field with only the "24954" portion. I need to JOIN on these 2 fields somehow.
So an example would be the following:
OrderTable1.Full_Order_No: BL-53151-24954-1-0001-33934
OrderTable2.Order_No: 24954
SELECT
ot1.Full_Order_No
, ot2.Order_No
FROM
OrderTable1 ot1
INNER JOIN OrderTable2 ot2 ON ot2.Order_No = [do something here to truncate ot1.Full_Order_No]
How can I do this?
Few notes:
-the 1st part of the order number, "BL-53151-" will ALWAYS be the same. It's our client # which will never change for the purpose of this query.
-The portion I need (24954) can be more or less than the 5 current digits.
-There will always be 6 portions to the order number, split up between 5 dashes.
I want to extract a number within a column results, that is the number between the first two commas
Example:
select path from tablea
results:
1158285,1158286,1158287,1158288,1158289
1158288,1158289,1158290,1158291,1158292
....
How to extract the second number(between the first two commas) from the above results?
The output should be
1158286
1158289
I am using SQL Server 2008. I have strings like this:
AB-123
CDW-32
declare @First_Part varchar(3)
declare @Second_Part varchar(5)
I want to split the string with delimiter '-' and store as first part and second part.
I saw few sample functions to split a string but these return table of values.
I simply want first part and second part.
In above examples context:
@First_Part = 'AB'
@Second_Part = '123'
Any simple way to do this?
I have a text filed in my table.I have sample data looks like <<some status>> << 3/9/2008 10:00:45 PM>> <<personname>>Im interested in searching <<some status>> and <<personname>> together by skipping date in between so my query set should return status and same person name i m looking for.
View 11 Replies View RelatedHi all I was wondering whether it was possible to remove or delete part of a String in a Field? Lets take for example I have:
- A Table called: Table_1
- A Field called: MyField
- MyField contains the value: Hello I am on the msdn forums
Is it possible to perform an UPDATE Query which deletes part of that sentence? If this was hard coded it would be rather simple but if the phrase was changing which would lead to the sentence also changing is it possible to do this?
Lets say I wanted to remove the part which said msdn forums. Then UPDATE the field again which should leave out the bit msdn forums.
Appreciate the responses, Onam.