Find In String And Return Part Of String

Mar 21, 2007

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

View 7 Replies


ADVERTISEMENT

Return Part Of A String

Mar 21, 2007

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

View 2 Replies View Related

Help: About Ms Sql Query, How Can I Check If A Part String Exists In A String?

May 22, 2007

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

View 9 Replies View Related

Transact SQL :: Search And Return String After Searched String

Sep 1, 2015

Is there way to search for the particular string and return the string after that searched string

SalesID
Rejection reason
21812

[code]....

The timeout period elapsed hence disqualified

View 3 Replies View Related

SQL Server 2014 :: Find String With Spaces And Replace Them With Other String

Sep 8, 2015

I have following query which return me SP/Views and Functions script using:

select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like
Create proc
create procedure
create proc
create view
create function

I need its result as:

Alter Procedure
Alter Procedure
Alter Procedure
Alter View
Alter Function

I used following

select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?

View 5 Replies View Related

Part Of String

Nov 25, 2004

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

View 3 Replies View Related

Searching Part Of String With Sql

May 30, 2006

I 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 Related

Strip Off Part Of A String

Dec 4, 2000

I am trying to strip off 'XYZ' from column1 in table1 whenever it occurs
Any help appreciated
saad

View 4 Replies View Related

Extracting Part Of A String

Aug 22, 2006

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!

View 5 Replies View Related

How To Extract Part Of A String

Jul 23, 2005

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 Related

How To Extract Part Of String

Aug 22, 2007

Hi,


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.

View 8 Replies View Related

SELECT Part Of A String In A Column

Dec 23, 2013

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'.

View 5 Replies View Related

Question About Query Part Of A String

Aug 7, 2007

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 Related

Extrach Part Of A Column String Into Another Field

Feb 6, 2001

hi, 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

View 2 Replies View Related

SQL Server 2012 :: Group By Part Of String

Jan 8, 2015

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%'"

View 9 Replies View Related

SQL Server 2008 :: How To Extract Part Of A String

May 11, 2015

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.

View 2 Replies View Related

How To Extract Part Of A String In Column Results

Jul 21, 2015

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

View 2 Replies View Related

Transact SQL :: Split A String And Store As First And Second Part

Jul 17, 2015

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?

View 6 Replies View Related

Transact SQL :: Skip Part Of String While Searching Using LIKE

May 13, 2015

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 Related

Removing Or Replacing Part Of A String In A Field?

Mar 4, 2008

Hi 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.

View 5 Replies View Related

Updating Only A Small Part Of A Text String In A Field

Aug 17, 2006

Hello all,

I have a table that holds a large amount of text in a field that is the body of the email. For example, it might say something like:

Quote: Email tech support at thisemail@email.com if you have any questions about the results of this test.

I need to change the email address in this field. Using this example I need to change thisemail@email.com to thatemail@email.com; however I do not want to change the other text in that field.

It is also important to note that the rest of the body of the emails stored here is different depending on the email.

So basically what I need is a statement that would look at a particular field, search for an email address, and replace that email address with another one without disturbing the rest of the text in that field. I already checked the w3 update tutorial and the update there is for the entire field.

Thanks for the help in advance!

View 7 Replies View Related

SQL Server 2012 :: Replacing String (Particular Part Of A Row In A Table With New Value)

Oct 7, 2015

I'm trying to replace a particular part of a row in a table with a new value.

The row is called "DataPath" and it has a lot of values like so:

mashOperationsComponent Data FilesSantec

I want to run a query to replace the mash with our DFS namespace share name companysharesDepartments but keep everything else past the mash part of the original row.

I'm currently running this query, it says it is altering 30,000 rows, but it doesn't look like it's doing anything at all..

UPDATE dbo.Part
SET DataPath = REPLACE(DataPath,'company.localsharesDepartments','mash')
WHERE DataPath like 'mash\%'

So for example, it would change the mash above to

company.localsharesDepartmentsOperationsComponent Data FilesSantec

View 2 Replies View Related

Delete Right And Left Part And Show Middle String?

Oct 31, 2014

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

View 3 Replies View Related

Sql2k: Increment Numeric Part In Arbitrary String

Apr 11, 2003

CREATE FUNCTION fctisnumericex(@c varchar(1))
RETURNS int AS
BEGIN
RETURN CASE WHEN ASCII(@c)>=ASCII('0') AND ASCII(@c)<=ASCII('9') THEN 1 ELSE 0 END END

CREATE FUNCTION fctstringincrement (@string varchar(255),@maxlen int)
RETURNS varchar(255) AS
BEGIN
DECLARE @@posr int
DECLARE @@posl int
DECLARE @@c varchar(1)
DECLARE @@token1 varchar(255)
DECLARE @@token varchar(255)
DECLARE @@token3 varchar(255)
DECLARE @@i int
/* emulates parts of the behaviour of s_modformatting::substringincrement */
/* 1. find the place where the numeric token starts from the right */
/* if we didn't find any non-numeric part then it might well be that the rightmost digit is already numeric */
IF dbo.fctisnumericex(SUBSTRING(@string,DATALENGTH(@string),1))=1
BEGIN
SELECT @@posr=DATALENGTH(@string)
END ELSE BEGIN
SELECT @@i=DATALENGTH(@string)
SELECT @@c=SUBSTRING(@string,@@i,1)
WHILE dbo.fctisnumericex(@@c)!=1 BEGIN
SELECT @@i=@@i-1
IF @@i<1 BEGIN BREAK END
SELECT @@c=SUBSTRING(@string,@@i,1)
END
SELECT @@posr=@@i
END
/* so have we got any numeric part inside that string? */
IF @@posr>0 BEGIN
/* yep. see how long it lasts */
SELECT @@i=@@posr
SELECT @@c=SUBSTRING(@string,@@i,1)
WHILE dbo.fctisnumericex(@@c)=1 BEGIN
SELECT @@posl=@@i
SELECT @@i=@@i-1
IF @@i<1 BEGIN BREAK END
SELECT @@c=SUBSTRING(@string,@@i,1)
END
/* separate now the parts of the string */
IF @@posl>1 BEGIN SELECT @@token1=SUBSTRING(@string,1,@@posl-1) END ELSE BEGIN SELECT @@token1='' END
SELECT @@token=SUBSTRING(@string,@@posl,@@posr-@@posl+1)
IF @@posr<DATALENGTH(@string) BEGIN SELECT
@@token3=SUBSTRING(@string,@@posr+1,DATALENGTH(@string)-@@posr) END ELSE BEGIN SELECT @@token3='' END
/* increment the numeric part */
SELECT @@token=convert(varchar(255),convert(int,@@token)+1)
END ELSE BEGIN
/* no numeric part at all. start with 1 at the end */
SELECT @@token1=@string
SELECT @@token='1'
SELECT @@token3=''
END
/* recompose the string and trim to max length if necessary */
RETURN SUBSTRING(@@token1+@@token+@@token3,1,@maxlen)
END

View 4 Replies View Related

Transact SQL :: Extract ID And Specific Part Of A String From Column

Jun 10, 2015

I have to extract a specific part of a string from a column in a sql server table. Following are the details and I have given the sample table and the sample strings.

I have 2 columns in my table [dbo].[StringExtract] (Id, MyString)

The row sample looks like the following

I have to extract the Id and a part of the column from mystring.

Id      MyString
1      ABC|^~&|BNAME|CLIENT1||CLIENT1|20110609233558||BIC^A27|5014589635|K|8.1|
ABC1|^~&|BNAME1|CLIENT1||CLIENT1|20110609233558||CTP^A27|5014589635|I|7.1|
DEF||5148956598||||Apprised|Bfunction1||15|LMP|^^^201106101330|
alloys3^ally^crimson^L||||alloys3^ally^crimson^L||||alloys3^ally^crimson^L|||||Apprised|

[Code] ....

The part I want to extract is in the line "ZZZ" and the string part that i want to extract is between the 5th and 6th pipes (|). So my output looks like the following

Id      DesiredString
1      Extracts^This^String1
2      Extracts^This^String2
3      Extracts^This^String3

Is there a way to extract this either using TSQL or SSIS.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U'))
DROP TABLE [dbo].[StringExtract]
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[StringExtract](
[Id] [int] NULL,

[Code] ....

View 4 Replies View Related

Transact SQL :: REPLACE Part Of String With Different Piece Of Text

Apr 20, 2015

I have a string column in a DB where it's values contain the following midway through the string ([DOCUMENTGUID] is a uniqueidentifier that is different for each row):

<a href="../downloadDoc.aspx?dg=[DOCUMENTGUID]" target="_blank">

I would like to replace this part of the string with a different piece of text.

I know that I should be using PATINDEX and REPLACE functions but I don't know how to utilise.

View 4 Replies View Related

Return Dat In String

Jul 25, 2007

hi! how would i return the current day in string format?

the output should be sun,mon,tue,wed,thu.. etc?

pls. help.. thanks

View 3 Replies View Related

Return Into One String

May 5, 2006

I have a table with a column called AccessTypes which contains a singleletter.I want to return these accesstypes from a query into one string. e.g.if there were 3 entries of A, S and DI want to select them and instead of returning 3 rows, I want just 1string like "ASD"can it be done?

View 3 Replies View Related

SQL Server 2012 :: Select Case Statement To Remove Part Of String After One Or Two Specific Characters

Jun 3, 2015

I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'

Example Record 1. Elland **REQUIRES BOOKING IN***
Example Record 2. Theale, Nr Reading, Berkshire
Example Record 3. Stockport

How do I achieve this in a CASE Statement?

The following two case statements return the correct results, but I some how need to combine them into a single Statement?

,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0
THEN LEN(Address )
ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'

,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0
THEN LEN(Address)
ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'

View 8 Replies View Related

Sqldatasource Return Into A String

May 28, 2007

Hi..
how can i insert the sqldatasource return into a string
 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CommerceTemplate %>" SelectCommand="SELECT COUNT(productID) FROM CSK_Store_Product"></asp:SqlDataSource>
this consults return a number of elements in the table, i need insert this number into a string
Thanks...

View 2 Replies View Related

Sql String Find With &&

Jun 22, 2007

I have a visual studio 2005 app that has to match strings to something in a database.
It works wonderfully until a string shows up  with an & in it (so "this & that")
The app wont find anything even though it should, the same query that is made in VS works just fine in enterprise manager
I tried a replace on the & with the chr(), hex, and html reference for & and none of those work.
We tried using different methods in VS to do the sql statement and commands.
The statement surrounds the string with single quotes.
If we take the & out, everything works fine.
Turned off request valadation just in case.
Any ideas?????

View 8 Replies View Related

Find String

Jul 20, 2005

I want to find if a data string is somewhere in a table but i dontknow the table of the database or the field. Who can i searchanywhere?ThanksBrainjk

View 1 Replies View Related

Sending Commands To SQL And Put The Return Value To String

Apr 29, 2007

ASP2.0 .NET MS Web Developer express, MS SQL express. 
with SqlDataSource control, i established a connection to database, now without Data controls like gridview, etc, can i get data from the database using commands (sql statements) ? how?
 say i have SqlDataSource, i want to get the first row first column data to pass it through (response.redirect()), is that possible and how?
 
i really appriciate you help

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved