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