String Manipulation Question

Mar 31, 2008

Greetings all,

What's the neatest way to get the whole string but the last word?

e.g.

'THE CAT SAT ON THE MAT' would become 'THE CAT SAT ON THE'

My solution :


select left(@test, len(@test)-charindex(' ', reverse(@test)))


Thanks in advance.

View 1 Replies


ADVERTISEMENT

Need Help With String Manipulation - Splitting 1 String Into Multiple Columns

Sep 11, 2006

Hello All,

I'm a non-programmer and an SQL newbie. I'm trying to create a printer usage report using LogParser and SQL database. I managed to export data from the print server's event log into a table in an SQL2005 database.

There are 3 main columns in the table (PrintJob) - Server (the print server name), TimeWritten (timestamp of each print job), String (eventlog message containing all the info I need). My problem is I need to split the String column which is a varchar(255) delimited by | (pipe). Example:

2|Microsoft Word - ราย�ารรับ.doc|Sukanlaya|HMb1_SD_LJ2420|IP_192.10.1.53|82720|1

The first value is the job number, which I don't need. The second value is the printed document name. The third value is the owner of the printed document. The fourth value is the printer name. The fifth value is the printer port, which I don't need. The sixth value is the size in bytes of the printed document, which I don't need. The seventh value is the number of page(s) printed.

How I can copy data in this table (PrintJob) into another table (PrinterUsage) and split the String column into 4 columns (Document, Owner, Printer, Pages) along with the Server and TimeWritten columns in the destination table?

In Excel, I would use combination of FIND(text_to_be_found, within_text, start_num) and MID(text, start_num, num_char). But CHARINDEX() in T-SQL only starts from the beginning of the string, right? I've been looking at some of the user-defind-function's and I can't find anything like Excel's FIND().

Or if anyone can think of a better "native" way to do this in T-SQL, I've be very grateful for the help or suggestion.

Thanks a bunch in advance,

Chutikorn

View 2 Replies View Related

String Manipulation

Apr 2, 2001

I am totaly confused please help

I am trying to change a tring of 7 characters of the format "XXYZZZZ" to be
"XX0YZZZZ" I wonder if any body has any idea

Also how can get an out of a dattime field in the format of DDMMYYYY and converted into text.

You help is highly appreciated

View 1 Replies View Related

STRING MANIPULATION

May 15, 2001

Hi!

I am using the follwing query for extracting the country name and city in a COLUMN [Destination Name] in Destinations table. The Data in the table looks like:

CANADA - Toronto
United States- ARIZONA
France
Argentina
United States (USA)- ARIZONA
........
........

The folowing query is producing the required results upto soem extent but without using -1 in subtracting the one value of CHARINDEX. The error is:

Server: Msg 536, Level 16, State 4, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.

QUERY
-----
select

Left(
Destinations.[Destination Name],
charindex("-", Destinations.[Destination Name])-1
)
as test
into temp2
from destinations

CAN ANYBODY HELP me in extracting the city an dcoutry name. I also want to delete the name in () like (USA).

View 1 Replies View Related

String Manipulation ?

Nov 30, 2004

I was given a script that was supposed to take a name field that was separated by commas and normalize it into last, first and middle name. My data looks like below in one fieldname called longname

crab,mike,Allen
Lota Weilly,Eric,M

My script to do this looks like

update ailoca
set last_name = substring (longname, 1, patindex( '%,%' , longname) -1 ),
first_name = substring (longname, patindex( '%,%' , longname) + 1, patindex( '% %', longname)-patindex( '%,%' , longname)),
middle_name = substring (longname, patindex( '% %', longname) + 1, len(longname)-patindex( '%,%' , longname))

My problem is that some people actually have 2 last names, not hyphenated, but 2. Whenever I have 2 names I get the following error

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.

It seems to be related to the first name, I can comment out that update and it works

Thanks
Thanks

View 11 Replies View Related

String Manipulation

Jul 24, 2007

Hi All,

I am trying to break the string that looks like this


2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation.
2007-05-06 07:36:21.28 server All rights reserved.
2007-05-06 07:36:21.28 server Server Process ID is 292.

into three separate strings to look like this

col1 col2 col3
2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation.
2007-05-06 07:36:21.28 server All rights reserved.
2007-05-06 07:36:21.28 server Server Process ID is 292.


I was able to separate the above string into two columns, but can't figure out how to put the rest of the string into the third column.

Any help is appreciated.

Thanks.

View 2 Replies View Related

String Manipulation

Nov 13, 2005

Field1 = Dominguez Public Transport Division 03 9320 4326

how do i remove these strings in T-SQL

Field1 = Dominguez
Field2 = Public Transport Division
Field3 = 03 9320 4326

View 3 Replies View Related

String Manipulation

Jul 24, 2007

Hi All,

I am trying to break the string that looks like this


2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation.
2007-05-06 07:36:21.28 server All rights reserved.
2007-05-06 07:36:21.28 server Server Process ID is 292.

into three separate strings to look like this

col1 col2 col3
2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation.
2007-05-06 07:36:21.28 server All rights reserved.
2007-05-06 07:36:21.28 server Server Process ID is 292.


I was able to separate the above string into two columns, but can't figure out how to put the rest of the string into the third column.

Any help is appreciated.

Thanks.

View 3 Replies View Related

String Manipulation

May 3, 2008

Sir

How i m Manipulate String , I have
(ABC,XYZ,EFG) this string
know i want to break this string 1 by 1 and modify and then rearrange in same form ang Get

like (ABCWE,XYZRT,EF)
pls help me out

Yaman

View 2 Replies View Related

String Manipulation

May 29, 2008

Hi
I have a field which is a file path
like
'C:avde8393948.txt'

I want to separate them into folder and filename
now the filename is always the same length,
so i can use RIGHT to get the filename,
but i prefer a method that from the right detects the
1st occurance of and everything after is the filename,

View 2 Replies View Related

String Manipulation

Aug 29, 2007

Hi If i have a string like :

AX4030303022

in one field and need to split it into

AX 030303022

I can use LEFT(field_name,2) for the 1st one, but
what can i use for the 2nd ?
and how can i deal with NULLS ?
if its NULL i want a blank space..

View 16 Replies View Related

String Manipulation SQL

Jul 20, 2005

We have some rows that we need to do some tricky string manipulationon.We have a UserID column which has userid entries in the formatfirstname.lastname and i need to change each entry tolastname.firstnameCan this be done by some script?Thanks so much for your help.Sid

View 3 Replies View Related

String Manipulation

Aug 13, 2007

Hi all,

I am having problem in string manipulation in SSIS - Derived Column Transformation.
I am trying to extract the OU names from Active Directory objects into a SQL table.

Assume that a distinguish name (DN) of an object as below:
CN=John, Doe,OU=Users,OU=SubOU,OU=ParentOU,DC=domain,DC=company,DC=com

How can I manipulate the above string so that I get:
ParentOU/SubOU/Users

Thanks in advance. Help is much appreciated!

View 8 Replies View Related

Dynamic String Manipulation

Sep 15, 2015

I have the following data in a table;

Create Table #Table (Column1 Varchar(1000))
INSERT INTO #Table
Select 'Execute Procedure @param1 = 100, @param2 = 1000, @param3 = ''longtext'' '
UNION ALL
Select 'Execute Procedure @param1 = 10, @param2 = 1000, @param3 = ''longtext'' '
UNION ALL
Select 'Execute Procedure @param1 = 100000, @param2 = 1000, @param3 = ''longtext'' '
Select * from #Table

I want to get rid of @Param1 in every row of the table so my final results look like this.

Create Table #FinalTable (Column1 Varchar(1000))
INSERT INTO #FinalTable
Select 'Execute Procedure @param2 = 1000, @param3 = ''longtext'' '
UNION ALL
Select 'Execute Procedure @param2 = 1000, @param3 = ''longtext'' '
UNION ALL
Select 'Execute Procedure @param2 = 1000, @param3 = ''longtext'' '
Select * from #FinalTable

The tricky part is that the @param1 value length varies so a straight forward substring or replace function won't work.

View 1 Replies View Related

String Manipulation Challenge In SQL

Oct 27, 2005

I have a sql query in which I need to isolate part of the columm valueand return only that isolated portion. I can only do this within theselect statement, and cannot add a function or anything like that. Iwould also like to keep this query within sql (I don't want to do thisin my programming environment)The string value would normally look like "segment1-segment2-segment3".I need to isolate segment2, but I have to be able to account forsituations in which either one or both dashes are missing (in whichcase returning "" or the whole string is OK. The best I have been ableto do reliably is to get "segment2-segment3".Anybody want to take a stab?

View 4 Replies View Related

String Manipulation + Formatting

Dec 9, 2007

Is there anything in SSIS that enables simple string reformatting?

For example I'd like to convert the character string 1234567.89 to 1,234,567.89.

I used to do this with an edit pattern in Cobol, but I can't see anything in the Expressions editor....

View 4 Replies View Related

String Manipulation Within Stored Proc

Jan 21, 2005

Hi Guys,

I have an nVarChar field named "Event" (I know - I didn't name it !) with variable length values such as *ALARM* or *RESTORE*

They always start or end with a * and I want to trim them off before returning the data to my app.

I've got rid of the first one with...


LTRIM(STUFF(Event, 1, 1, ''))


Any tips on how to get rid of both of them in one go ?

Thanks in advance.

Steve.

View 2 Replies View Related

String Manipulation With Web Form Query

Feb 24, 2006

I'm trying to look up customer records by e-mail domain by using a text box on a Web form. So if I want to look for all my customers that have an aol e-mail domain, I would type aol.com in the text box and the sub routine would know to count 7 characters from the right and through those characters into maybe a parameter query. I'm having problems passing this in. I can count the characters properly by using:
dim strText = MyTextBox.Textdim intLength = strText.Length
but having problems starting here......
MyCommand.SelectCommand.Parameters("@email").Value = MyTextBox.Text
..............
but how would I ultimately feed this into my sql satement? Select * from Customers Where email = right(@email,intLength)
Help appreciated.
Frank
 
 

View 1 Replies View Related

Best Way To Do Large String Manipulation - URGENT !!!

Oct 24, 2001

Hi,

I am building a string (basically XML) on the fly in stored procedure (SQL 7.0). I am using one local variable @str_xml varchar(8000) to build this string.
Now my problem is I can store maximum upto 8000 characters in "varchar type". And I can't use text field as SQL Server doesn't allow "text type" to be used as local variable.
I am thinking to use 2 or 3 local varchar variables and then return them separately to front end and will do contatination at the front end. But I think it would be ugly way to do as I have to check every time in stored procedure the length of string.

Any suggestions to do this in some other way would be greatly helpful to me.

Thanks
Dinesh

View 1 Replies View Related

First Time Writing, String Manipulation??

Mar 5, 2007

I'm hoping someone can help! Im using sql2000, and I am attempting to capitalize every 1st letter of a word in a column.

For Example:
"GOLF IS FUN,BOWLING IS GREAT"

What Id like to get as my results:

"Golf is fun, Bowling is great"


Trying to figure out the syntax to get the character after the comma to have a space then capital "B" Thought I could use a charindex but just cant seem to get it.

View 4 Replies View Related

Stored Procedure String Manipulation

Nov 2, 2006

I am somewhat new to the world of programming with SQL Server and was wondering if this could be done. Well I know it can be done but was wondering how it might be done.

I have a DTS package created to import a table from and AS400 server. What I need to do is take one field and parse that field into 5 different values for 5 new fields.

Here is what I know needs to be done but not sure how to put into the procedure.

CREATE proc ChangeHIS

as
--Declare Variables
Declare @LastName varchar,
@FirstName varchar,
@MI varchar,
@ID varchar,
@Dept varchar,
@intCount int,
@UserName varchar,
@strTemp varchar

--Create Temporary Table

CREATE TABLE [EmployeeAudit].[dbo].[tmpTable] (
[UPUPRF] varchar (10),
[UPTEXT] varchar (50)
)

select [UPUPRF], [UPTEXT] from tblHIS into tmpTable

GO

And something dealing with the below code as well.

@tmpString = RTRIM(LTRIM(@tmpString))

If charindex(@tmpString, ",") > 0
--'Manuel, Michael J - 78672 - SR MIS SUPPORT SPEC'
@LastName = Left(@tmpString, charindex(@tmpString, ","))
@tmpString = RTRIM(LTRIM(Right(@tmpString, Len(@tmpString) - charindex(@tmpString, ",") + 1)))
--'Michael J - 78672 - SR MIS SUPPORT SPEC'
@FirstName = Left(@tmpString, charindex(@tmpString, " "))
@tmpString = RTRIM(LTRIM(Right(@tmpString, Len(@tmpString) - charindex(@tmpString, " ") + 1)))
If charindex(@tmpString, "-") > 1
--'J - 78672 - SR MIS SUPPORT SPEC'
@MI = Left(@tmpString, 1)
@tmpSting = RTRIM(LTRIM(Right(@tmpString, Len(@tmpString) - 2)
End
--'- 78672 - SR MIS SUPPORT SPEC'
@ID = Left(@tmpString, charindex(@tmpString, " - "))
@tmpString = RTRIM(LTRIM(Right(@tmpString, Len(@tmpString) - charindex(@tmpString, " - ") + 3)))
--'SR MIS SUPPORT SPEC'
@Dept = @tmpString
End

Hope someone can point me in the right direction

View 13 Replies View Related

Find And Replace String Manipulation Help

Mar 14, 2008

Please help me with the sql script to manipulate the string data:

I need to add <Text> at the beginning and end of the string.
I also need to add <option> before the first and after last occurence of the <Option> string. The original string

<StockNumber>502</StockNumber>
<OptionKey>113</OptionKey>
<OptionKey>151</OptionKey>
<Warranty>1</Warranty>

should look like

<Text>
<StockNumber>502</StockNumber>
<Option>
<OptionKey>113</OptionKey>
<OptionKey>151</OptionKey>
<Option>
<Warranty>1</Warranty>
<Text>


Thanks.

View 6 Replies View Related

SELECT Items Based On String Manipulation

Oct 25, 2005

I am having some trouble creating a query that will preform some string manipulations on a field, and include this as part of the WHERE clause

For example I want to do something like this:


Code:

SELECTTable1.Column1,
Table1.Column2,
Table1.Column3,
Table2.Column1

FROMTable1
INNER JOIN Table2 ON Table1.UID = Table2.UID

WHERE(SET @Temp = Table2.Column1

--remove all 0's
SET @k = patindex('%[^0 ]%', @Temp)
WHILE @k> 0
BEGIN
SET @Temp = replace(@Temp, substring(@Temp, @k, 1), '')
SET @k= patindex('%[^0 ]%', @Temp)
END
SELECT @Temp
) = ''



But of course this isn't working so much. I am wondering if I have to use a cursor?

View 1 Replies View Related

T-SQL (SS2K8) :: String Manipulation - Data Between Quotes

Apr 9, 2015

I have data in a trace file, and I need to extract some info such as phone number.The problem is the phone number could be varying lengths, and various positions in the row.

For example:

@City='New York', @Phone='2035551212' (10 characters, no dashes)
or
@City='San Francisco', @Phone='918-555-1212' (12 characters, with dashes)
or
@City+'Berlin', @Phone='55-123456-7890' (14 characters, with dashes)

I can use CHARINDEX to search & find @Phone=' so I know where the phone number starts, but stuck on a programatic way to find the data between the quotes since it can vary.

View 4 Replies View Related

String Manipulation From Flat File Into Database

Jan 9, 2008



Hi

I'm used to DTS but new to SSIS. What's a good reference/tutorial that deals with transforming columns of data (from a flat file) from one format to another when uploading into SQL2005? Typically columns of data have "" around the values and spaces that I want to remove.

Presumably in SSIS I need the following:

A Data flow task containing:

Flat file source
Derived or Copy Column?
OLE DB Destination

Is this on the right track?

Thanks
Gerry

View 1 Replies View Related

SQL Server 2012 :: Hyphen Delimited String Manipulation

Oct 19, 2015

I am having a field 'Flight Route that holds hyphen delimited character sequences.

E.g. : ABC-BCD-DEF-EFG.

My requirement is like this:

If the flight route is:

ABC-BCD-BCD-DEF make it ABC-BCD-DEF
ABC-ABC-BCD-DEF make it ABC-BCD-DEF

i.e. 'whenever a sequence repeats,only one appearance of that sequence should be displayed.The field Flight Route has to be updated with this replaced string.

View 6 Replies View Related

Transact SQL :: String Manipulation Single To Multiple Rows

Nov 7, 2015

The recipe preparation instructions are stored in a table by RecipeID.  The prep instructions are in a single VARCHAR(MAX) column and look something like this:  

1.  Boil Water 
2.  Add noodles 
3.  Add cheese sauce 
4.  Stir well

Now they want this single VARCHAR(Max) column broken into 2 columns - Step and Prep Instruction like this: Boil WaterAdd noodlesAdd cheese sauceStir well.I figure I can use the appearance of a number followed by a period and a space to determine the existence of a new row.  How would I accomplish this in T-SQL?

View 11 Replies View Related

Row Manipulation

May 29, 2008

col1 col2 col3
1IN NULL
2FOR 1
3Small 4
4World 1
5Hj 1
6NJ 1
7Welcome 1
8So 4
9We 1
10Are 1
11Resp 3
12OIJIOJPOJPJO7
13OIJOIJ 1
14LKJ 2
15IJ 1

Is there any way, to query the above table which returns me all the 1's inside col3 until it finds any other number than 1 + the previous row preceding the 1
for eg., my 1st set would be
1IN NULL
2FOR1
2nd set would look something like this
3Small 4
4World1
5Hj 1
6NJ 1
7Welcome1
3rd set
8So4
9We1
10Are1
4th Set
12OIJIOJPOJPJO7
13OIJOIJ1

and the last set
14LKJ2
15IJ1

Any help is appreciated!!

View 1 Replies View Related

Dataset Manipulation

Mar 1, 2007

Have an XML file that i load in to a Dataset. works fine, it builds its own scheme perfectly.
I loop through that data to load a check list which also works wonderfully.
 two questions based on that.
1) can i add a column after the fact? I want to basically update the the info in the dataset to store if the record was checked in the check list. if not, i can manipulate one of the already defined columns, but i would rather not.
2) what is the best way to update data with in the dataset? Never really done anything but pull data from one. how do i locate the correct row to update ("select name from tbl where name = " + checklist.items[index].tostring(); update row)
 
sorry for the fairly basic question. i appreciate the help.
 Justin
 

View 2 Replies View Related

Record Manipulation

Feb 3, 2003

Hi,

I have a table that has about 12 fields and 700000 records. My client is going to send me 4 fields out of the 12 fields (1 of them will be primary key) in a text format. What is the best way I can get them updated in the table?

Please help!!

Thanks!!

View 1 Replies View Related

Text Manipulation

Apr 30, 2008

Hi, is there a way i can replace multiple white spaces & tabs into one , on a TEXT column ?

I am using replace function and trying to store the result into a varchar variable and the TEXT being truncated after 255 charecters.

for example, I tried this :

declare @str varchar(4000)
select @str = replace(cast(textcolumn as varchar(4000)), ' ', NULL)

I understand t-SQL dml statement(s)
will automatically truncate after 255 chars in a long string. But, seeking for other options here pls.

thanks for ur help..

View 6 Replies View Related

Using Cursor For Manipulation

Oct 17, 2014

ALTER procedure [dbo].[sp_debit_credit]

as begin
SET NOCOUNT ON
DECLARE @DEBIT_ID INT
DECLARE @CREDIT_ID INT
DECLARE @servicedamount decimal(18,2)
declare @bal_Serviced_Amt decimal(18,2)

[Code] ....

table is like this
------------------------
DEBIT.ID, DEBIT.ACCOUNT_NO,DEBIT.SERVICED_AMT,CREDIT.ID,CREDIT.TRAN_AMT,CREDIT.SERVICED_FLAG,credit.Serviced_Amt
1456050866223983.0006418110.000300000.00
1456050866223983.0006419110.000500000.00
1456050866223983.0006447110.0002800000.00
1456050866223983.0006510110.0003100303.00

[Code] ....

I need result, I have to subtract

(DEBIT.SERVICED_amt - CREDIT.TRAN_AMT) and set CREDIT.SERVICED_FLAG =1 ,
if DEBIT.SERVICED_amt = CREDIT.TRAN_AMT then set debit.SERVICED_FLAG =1

Using with cursor. It will check row by row , i have written stored procedure , but flag is not updated .

View 1 Replies View Related

Date Manipulation

Jul 20, 2007

I have two tables TableA and TableB as shown in the example below. I will have to Update TableA based on the data in TableB.
If a member in TableB has an EmployerId different from the one in TableA where the EffectiveDate in TableB between the EffectiveDate and ExpirationDate in TableA, then it should void the row in TableA and create rows as shown in the example below (Please refer to the 1st record and the last three records).

I would like to get some suggestions on how to do this efficiently. I am not looking for queries, but I need some ideas...

Thanks in advance.

TableA

MemberId EmployerId EffectiveDate ExpirationDate VoidIn
12345 111 1/1/2006 10/31/2006 0
12345 222 11/1/2006 5/31/2007 0
12345 333 6/1/2007 12/31/9999 0


TableB

MemberId EmployerId EffectiveDate
12345 444 4/1/2006


TargetTable (TableA Updated)

MemberId EmployerId EffectiveDate ExpirationDate VoidIn
12345 111 1/1/2006 10/31/2006 1
12345 222 11/1/2006 5/31/2007 0
12345 333 6/1/2007 12/31/9999 0
12345 111 1/1/2006 3/31/2006 0
12345 444 4/1/2006 4/30/2006 0
12345 111 5/1/2006 10/31/2006 0



Thanks
Suresh

View 4 Replies View Related







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