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


ADVERTISEMENT

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

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

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

SQL String Into Stored Procedure

Apr 5, 2006

Not sure this is the right forum as I'm not sure quite what the problem is, but I have a feeeling it's the stored procedure that I'm using to replace the SQL string I used previously.I have a search form used to find records in a (SQL Server 2005) db. The form has a number of textboxes and corresponding checkboxes. The user types in a value they want to search for (e.g. search for a surname)  and then selects the corresponding checkbox to indicate this. Or they can search for both surname and firstname by typing in the values in the correct textboxes and selecting the checkboxes corressponding to surname and firstname.The code to make this work looks like this:----------------------------------------        Dim conn As SqlConnection        Dim comm As SqlCommand        Dim param As SqlParameter        Dim param2 As SqlParameter        Dim param3 As SqlParameter        Dim param4 As SqlParameter        Dim objDataset As DataSet        Dim objAdapter As SqlDataAdapter        conn=NewSqlConnection("blah, blah")        comm = New SqlCommand        'set properties of comm so it uses conn & recognises which stored proc to execute        comm.Connection = conn        comm.CommandText = "SPSearchTest3"        comm.CommandType = CommandType.StoredProcedure        'create input parameter, set it's type and value        param = comm.CreateParameter        param.ParameterName = "@empid"        param.Direction = ParameterDirection.Input        param.Value = txtPatID.Text        param2 = comm.CreateParameter        param2.ParameterName = "@LastName"        param2.Direction = ParameterDirection.Input        param2.Value = txtSurname.Text        comm.Parameters.Add(param)        comm.Parameters.Add(param2)        conn.Open()               objAdapter = New SqlDataAdapter(comm)        objDataset = New DataSet        objAdapter.Fill(objDataset)        dgrdRegistration.DataSource = objDataset        dgrdRegistration.DataBind()        conn.Close()------------------------------------While the stored procedure is this:------------------------------    @EmpID int,    @LastName nvarchar(20)    ASSELECT EmployeeID,    LastName,    Firstname,    BirthDate,    Address,    title,    addressFROM employeesWHERE (DataLength(@EmpID) = 0 OR EmployeeID = @EmpID)AND (DataLength(@LastName) = 0 OR LastName = @LastName)------------------------------This will work if I search using EmployeeID and Surname or only by EmployeeID, but I don't get any results if I search only for Surname, even though I know the record(s) exits in the db and I've spelled it correctly. Can someone point out where I'm going wrong?(Incidentally if I have a procedure with has only one parameter 'surname' or 'employeeID', it works fine!)Thanks very much and sorry about the long-winded post.

View 6 Replies View Related

T-Sql Stored Procedure Comparing String

Feb 24, 2008

HiI have a problem trying to compare a string value in a WHERE statement. Below is the sql statement.  ALTER PROCEDURE dbo.StoredProcedure1(@oby char,@Area char,@Startrow INT,@Maxrow INT, @Minp INT,@Maxp INT,@Bed INT
)

ASSELECT * FROM
(
SELECT row_number() OVER (ORDER BY @oby DESC) AS rownum,Ref,Price,Area,Town,BedFROM [Houses] WHERE ([Price] >= @Minp) AND ([Price] <= @Maxp) AND ([Bed] >= @Bed) AND ([Area] = @Area)) AS AWHERE A.rownum BETWEEN (@Startrow) AND (@Startrow + @Maxrow)  The problem is the Area variable if i enter it manually it works if i try and pass the variable in it doesn't work. If i change ([Area] = @Area) to ([Area] = 'The First Area') it is fine. The only problem i see is that the @Area has spaces, but i even tried passing 'The First Area' with the quotes and it still didnt work.Please help, its got to be something simple.Thanks In Advance 

View 2 Replies View Related

How To Return A String From A Stored Procedure

Mar 29, 2004

Up till now I've used SP's for updates and only ever needed to return error messages.

Now I have an SP that checks and validates something and has to return a string containing the result, (always a string/varchar!)

It works fine in Query Analyzer, I just need a demo of how to incorporate it into a VB app.

Hope that makes sense.

Thanks
Mark

View 4 Replies View Related

Passing XML String To Stored Procedure

Nov 28, 2005

hi,i have a stored procedure that is used to insert the employee data into a EMPLOYEE table.now i am passing the employee data from sqlCommand.i have the XML string like this'<Employee><Name>Gopal</Name><ID>10157839</ID><sal>12000</sal><Address>Khammam</Address></Employee>' when i pass this string as sql parameter it is giving an error. System.Data.SqlClient.SqlException: XML parsing error: A semi colon character was expectedbut when i execute the stored procedure in query analyzer  by passing the same parameter. it is working.please reply me on gk_mpl@yahoo.co.in

View 1 Replies View Related

How Return String Value From Stored Procedure

Apr 3, 2008

This procedure gives a error : " Msg 245, Level 16, State 1, Procedure YAMAN, Line 16
Conversion failed when converting the nvarchar value 'user' to data type int. "
How can i return string value

ALTER procedure [dbo].[YAMAN]
(@username varchar(20),@active varchar(20))
as
begin
if exists (select username from aspnet_Users where username=@username)
begin
if @active=(select active from aspnet_Users where username=@username)
return 'already exist'
else
begin
update aspnet_Users set active=@active where username=@username
return 'update'
end
end
else
return 'user does not exist'
end

Yaman

View 2 Replies View Related

Build String In Stored Procedure

Nov 19, 2007

I have SQL table (tblUsers) and one of the fields holds the email address. I want to step through each record build a multiple email string to send to a lot of people. It would look like this

Str_email = Me@hotmail.com;Andy@Hotmail.com;Fred@Hotmail.com

I then want to pass Str_email back to an asp.web page

Can this be done in a stored procedure ?

View 5 Replies View Related







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