Parse Long String To Columns

Dec 31, 2007



I have a column with varchar(2000) that contains events with time-stamps. The data looks something like this for a record:

03:14:46: abc 03:14:47: def 03:14:59: xyz 03:15:17: zzz

I would like to parse out each time-stamp and event to a separate columns, like

col1= 03:14:46: abc
col2= 03:14:47: def
col3= 03:14:59: xyz
col4= 03:15:17: zzz

The number of events are dynamic so number of time-stamps with events can be anything from 2-30 of them.

Anyone would suggest how I can resolve this?

Thanks.

View 5 Replies


ADVERTISEMENT

Can You Parse Out A Text String &> 8000 Chars Long??

Mar 30, 2005

Any way to parse out a text value (not varChar, using text data type) that is > than 8000 characters long? I'm looping through 1 big string passed to the DB that is pipe delimited, but I find myself needing the substring function to keep track of which segment I'm acting on (after an update, I then need to take that segment and remove it from the string)...but the subString function won't take anything larger than 8000 chars.

Say I have this string that is text data type...

'aaa|bbb|ccc|ddd|....'

..and so on, surpassing 8000 char length, how could you parse it out using the pipes as the delimter, then do an Update using that segment? Afterward, return to that string and find the next segment, then use it, and so on (in a loop). I tried using an update to set the string = replace(string, segmentJustUsed, '') to "erase" it, but replace can't take text as the datatype. Any help? Hope this isn't to confusing.

View 3 Replies View Related

Separate A Long String And Put It Into Three Columns

Apr 17, 2015

How to separate a long string and put them into three columns.

For example string

Toronto|Ontario|Canada,Dallas|Texas|USA,New York|New York|USA,Windsor|Ontario|Canada

I have a table with 5 columns, 3 columns are City, State and Country.

I would like to separate them and put those into categories

Continent City State Country Added

Toronto Ontario Canada
Dallas Texas USA
New York New York USA

View 1 Replies View Related

Transact SQL :: Select And Parse Json Data From 2 Columns Into Multiple Columns In A Table?

Apr 29, 2015

I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:

I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.

1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B

If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.

2. My second question: How to i get around this error?

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B,  fnSplitJson2(A.ITEM6,NULL) C

I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.

View 14 Replies View Related

Function To Parse A String

Mar 13, 2008

Hope someone can help... I need a function to parse a string using a beginning character parameter and an ending character parameter and extract what is between them. For example.....

Here is the sample string: MFD-2fdr4.zip

CREATE FUNCTION Parse(String, '-' , '.')
.....
....
.....
END


and it shoud return 2fdr4

View 8 Replies View Related

Parse A String Using Charindex

Jan 15, 2008

Hi,

I've the following query. I'm using the yellow highlighted to join 2 tables, as these tables dont have a relationship between them.
The format of the name field is 'AAAA-BBBBBB-123'
here A will be only 4 chars, followed by '-' B can be any number of chars again followed by '-' and the last is the id which I'm using to do a join. This query will fail if the id is just 1 digit as its taking the last 3 chars as the id.
I dont know how to get the id from the right using charindex. Charindex would search for the first occurence of '-' from the right and get the chars after hypen i.e 123. How can this be achieved?


SELECT id AS 'ID',
name AS 'name',
sequence AS 'num'
FROM
FirstTable A
INNER JOIN SecondTable q
ON (CONVERT(INT,RIGHT(name,3))= a.id)
INNER JOIN ThridTable t
ON(t.id = q.id)
INNER JOIN FourthTable s
ON (q.name = s.name )
WHERE A.id = @ID
AND t.name=LEFT(s.name,((CHARINDEX('-',s.name))-1))
ORDER BY 'ID','num'


One more question on this is: Is this a good way of joining tables? If I dont use this I've a very large query containing unions. Which one should be bug-free and more efficient?

Thanks,
Subha

View 9 Replies View Related

Nested CASE To Parse A String

Feb 6, 2006

I am attempting to create a view to bring together multiple joined tables based ona one to many relationship

Eg Table1 contains code, address
Table2 contains code, financial details
table3 contains code, financial history

This view is then going to be used to update a table with only one record for each 'code'. i.e. NewTable = code, add1, add2, city, prov, postal, financialvalue, history value1, history value2

My current stumbling block is:


One of the fields in table1 is a free format address field (address).
eg. could be (street addres, city prov, postal)
or
could be (street address 1, address2, address 3, city prov, postal)

I want to be able to assign individual components of the address to corresponding fields

if # of commas = 2 then
address1 = substring(address,1, position of first comma)
cityprov = substring(address,possition of first comma, position of second comman)
postal = substring(address rest of field)

I have a UDF which returns the number of commas but I cannot figure out how to use either a nested case statement to parse the string...

ANy ideas on how best to accompish this?
( this table is needed for some leacy software which can only handle one record with all infor....

greg

View 2 Replies View Related

Parse Out All Email Addresses In A String

Jul 23, 2013

I have a string like this one in my column

Mike@yahoo.com, Bill@aol.com, Dan@yahoo.com, Frank@gmail.com

In my result set I need to display all email addresses that do not have the @yahoo.com domain.

View 7 Replies View Related

Parse A Numeric String From A Field

Jul 23, 2005

Hello All,I'm trying to parse for a numeric string from a column in a table. WhatI'm looking for is a numeric string of a fixed length of 8.The column is a comments field and can contain the numeric string inany positionHere's an example of the values in the column1) Fri KX 3-21-98 5:48 P.M. arrival Cxled ATRI #27068935 3-17-982) wed.kx10/26 Netrez 95860536Now I need to parse through these lines and return only the 8 digitnumbers in itThe result set should be2706893595860536This is what I've done so farDeclare @tmp table(Comments_Txt varchar(255))Insert into @tmpselect Comments_Txt from Reservationselect * FROM @tmp where Comments_Txtlike ('%[0-9][0-9][0-9][0-9][0-9][0**9]%')But it returns the entire comments field in the result set. What I needis a way to return just those 8 digits.Any Ideas??Thanks in advance!!!

View 2 Replies View Related

Query To Parse Out Values From One Column Into Different Columns

Feb 2, 2006

I have a table where different types of values are stored in one field, but I need to seperate them into different fields based on a value in another field.

For (hypothetical) example:

There is an existing table with following info in three columns:
userid record recordtag
1 joe 1
1 j 2
1 jr 3
2 bob 1
2 a 2
2 sr 3
where recordtag indicates (1 for first name, 2 for middle initial, 3 for suffix)

I need to query these records for a report so it the output is:

userID firstname middleinitial suffix
1 joe j jr
2 bob a sr


What's the most efficient approach to create a query that will give me desired results? I have managed to create a very complex query that derives tables for each column I want to create and queries off of that derived table for the 'record' value based on the 'recordtag' values for a given 'userid'. The query is extremely slow, so I know there's some better way out there to get the results I want. Any help would be greatly appreciated. Thanks.

View 3 Replies View Related

SQL Server 2012 :: Parse A String Every 6 Letters

Jan 28, 2015

How to parse a string to equal length substrings in SQL

I am getting a long concatenated string from a query (CTVALUE1) and have to use the string in where clause by parsing every 6 characters..

CREATE TABLE [dbo].[PTEMP](
[ID] [char](10) NULL,
[name] [char](10) NULL,
[CTVALUE1] [char](80) NULL
)

INSERT INTO PTEMP
VALUES('11','ABC','0000010T00010L0001000T010C0001')
select * from ptemp

After parsing I have to use these values in a where clause like this

IN('000001','0T0001','0L0001','000T01','0C0001')

Now ,the values can change I mean the string may give 5 values(6 character) today and 10 tomorrow.. So the parsing should be dynamic.

View 2 Replies View Related

Trigger To Parse A String From Insert Statement

Mar 22, 2006

I have two tables:tb_news---story_id-productlisttb_lookup---story_id-productwhen an insert command is run on tb_news, productlist field ispopulated with a value such as 'abc, def, de'when this happens, i need tb_lookup to be populated with seperaterecords for each product in productlist and the story_id from tb_news.Example:INSERT INTO tb_news (story_id, product_list)VALUES (12345, 'abc, def, de')Results:tb_news--12345, 'abc, def, de'tb_lookup--12345, 'abc'12345, 'def'12345, 'de'Ideally, I would like this to use recursion and give me tha ability tochange the delimiter at any time (might not always be a comma). someproducts may have a period in them. number of products is unknown andmight be 0 (field may be empty or NULL).

View 2 Replies View Related

How To Parse A String Column With Comma Delimited

Jul 20, 2005

Hi,I would like to parse out each value that is seperatedby a comma in a field and use that value to join to another table.What would be the easiest way to do so without having towrite a function or routine ?EX.Table AAACOL1 COL21 11, 124, 1562 11, 505, 600, 700, ...Table BBBCOL1 COL211 Desc11124 Desc124156 Desc 156

View 2 Replies View Related

Parse XML Attributes And Populate Columns Within DB With Stripped Data

Nov 16, 2011

I'm trying to write a stored procedure that will parse XML attributes and populate columns within a DB with the stripped data. I'm a complete novice who prior to this week knew nothing about SQL commands, My understanding at least is that I need to perform a bulk insert.

Example XML file:

<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE Asset_Collection SYSTEM "Asset_Collection.dtd">
<Asset_Collection>
<Collection_Metadata
Name="Asset Collection"
Description="Random XML Feed Test"

[Code] ....

Table/Columns which need to be inserting into:
Table:
TABLE_A

Columns:
ProdID
CustomerID
AreaCode

View 4 Replies View Related

Parse Delimited Data In Column To Multiple Columns

Jun 13, 2008

I'm working on a sales commission report that will show commissions for up to 5 sales reps for each invoice. The invoice detail table contains separate columns for the commission rates payable to each rep, but for some reason the sale srep IDs are combined into one column. The salesrep column may contain null, a single sales rep id, or up to five slaes rep IDs separated by the '~' character.

So I'd like to parse the rep IDs from a single column (salesreplist) in my invoice detail table (below) to multiple columns (RepID1, RepID2, RepID3, RepID4,RepID5) in a temp table so I can more easily calculate the commission amounts for each invoice and sales rep.

Here is my table:

CREATE TABLE invcdtl(
invoicenum int,
salesreplist [text] NULL,
reprate1 int NULL,
reprate2 int NULL,
reprate3 int NULL,
reprate4 int NULL,
reprate5 int NULL,
)

Here is some sample data:

1 A 0 0 0 0 0
2 0 0 0 0 0
3 I~~~~ 15 0 0 0 0
4 A~B 5 5 0 0 0
5 I~F~T~K~G 5 5 2 2 2
6 A~B

As you can see, some records have trailing delimiters but some don't. This may be a result of the application's behavior when multiple reps are entered then removed from an invoice. One thing for sure is that when there are multiple reps, the IDs are always separated by '~'

Can anyone suggest a solution?

View 3 Replies View Related

T-SQL (SS2K8) :: How To Parse A String To Equal Length Sub-strings

Jan 28, 2015

How to parse a string to equal length substrings in SQL

I am getting a long concatenated string from a query (CTVALUE1) and have to use the string in where clause by parsing every 6 characters..

CREATE TABLE [dbo].[PTEMP](
[ID] [char](10) NULL,
[name] [char](10) NULL,
[CTVALUE1] [char](80) NULL
)
INSERT INTO PTEMP
VALUES('11','ABC','0000010T00010L0001000T010C0001')
select * from ptemp

after parsing I have to use these values in a where clause like this

IN('000001','0T0001','0L0001','000T01','0C0001')

Now ,the values can change I mean the string may give 5 values(6 character) today and 10 tomorrow..So the parsing should be dynamic.

View 2 Replies View Related

Stored Procedure To Parse Delimited String To Table?

Apr 10, 2015

SP to parse a delimited string and insert the result in a table. I am using SQL Server 2008 R2. I have 2 tables - RawData & WIP. I have Robots on a manufacturing line capable of moving data to a DB. I move the raw data to RawData. On insert [RawData], I want to parse the string and move the contents to WIP as indicated below. I will run reports against the WIP Table.

Also, after the string is parsed, I'd like to change the Archive column, the _0 at the end of the raw string to 1 in the WIP table to indicate a successful parse.

Sample Strings - [RawData Table]
04102015_114830_10_013_9_8_6_99999_Test 1_1_0
04102015_115030_10_013_9_8_6_99999_Test 2_1_0

Desired Output - [WIP Table]

Date Time Plant Program Line Zone Station BadgeID Message Alarm Archive
-----------------------------------------------------------------------------------
04102015 114830 10 13 9 8 6 99999 Test 1 1 1
04102015 115030 10 13 9 8 6 99999 Test 2 1 1

View 16 Replies View Related

SQL Server 2012 :: Parse Two Delimited Table Columns Into Multiple Records

Oct 22, 2014

I have a table structure where there are multiple "/" separated values in two columns that I need to parse out into single records.

CREATE TABLE CONFIGNEW(PlanID VARCHAR(100), GroupID VARCHAR(6), SubGroupID VARCHAR(255), AddOnCode VARCHAR(2), ExternalCode VARCHAR(20)
INSERT INTO CONFIGNEW(PlanID, GroupID, SubGroupID, ExternalCode) VALUES('101/201', '000005', 'LAA/OCA/UCA/XCA', '1', 'M231_1)

[Code] .....

The results I am looking to achieve are:

PLanIDGroupIDSubGroupIDAddOnCodeExternalCode
101000005LAA1M231_1
101000005OCA2M231_2
101000005UCA3M231_3
101000005XCA4M231_4
201000005LAA1M231_1
201000005OCA2M231_2
201000005UCA3M231_3
201000005XCA4M231_4

Is there an SQL statement that can be used to accomplish this?

View 1 Replies View Related

SQL Server 2008 :: Using Left And Charindex To Parse String / Getting Rid Of Rest Of Data

Jun 16, 2015

I am trying to erase some erroneous bad data in my table. The description column has a lot of </div>oqwiroiweuru</a> weird data attached to it, i want to keep the data to the left of where the </div> erroneous data begins

update MyTable
set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid = 1

that totally works.

update MyTable
set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid >= 2

gives me a Invalid length parameter passed to the LEFT or SUBSTRING function. The statement has been terminated error.

View 2 Replies View Related

SQL String Too Long?

Dec 28, 2006

My SQL statement: 
SQL Server seems to think my SQL Statement is too long........ I'm not sure why. 
"INSERT INTO tblChangeControls (Initiator,BPM,AddRemMod,ChangeType,SpecificChange,ChangeDescription,TechnicalDescriptionOfChange,DateInitiated) select 'Benjamin Short' ,'6' ,'2' ,'Printer' ,'40' ,'' ,'';"
Error message: 
The identifier that starts with 'INSERT INTO tblChangeControls (Initiator,BPM,AddRemMod,ChangeType,SpecificChange,ChangeDescription,TechnicalDescriptionOfChange,' is too long. Maximum length is 128.

View 2 Replies View Related

Cannot Save A Long String

Aug 7, 2007

This is what I am trying to save and what is actually being saved:

All variables are STRINGS.

Saving: 08:02:40 08:18:05 ^09:34:07 09:39:25 ^10:02:50 10:18:19 10:37:33 11:01:34 11:23:26 11:50:47 ^12:52:30 13:42:37 ^14:41:04 ^18:36:13
Saved: 08:02:40 08:18:05 ^09:34:07 09:39:25 ^10:02:50 10:

Saving: 08:08:52 08:59:55 ^09:38:04 09:54:20 ^10:15:37 10:34:40 10:39:02 11:12:45 11:39:33 ^12:18:51 13:11:24 ^14:04:52 15:18:43 ^19:13:10
Saved: 08:08:52 08:59:55 ^09:38:04 09:54:20 ^10:15:37 10:

The codes:

Table scripted to a query:

USE [Analytical]
GO
/****** Object: Table [dbo].[DailyTickMinMaxAnalysis] Script Date: 08/07/2007 15:44:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DailyTickMinMaxAnalysis](
[symbol] [varchar](50) NOT NULL,
[cDate] [varchar](50) NOT NULL,
[part] [varchar](50) NOT NULL,
[collection] [varchar](max) NULL,
CONSTRAINT [remPat_Trades] PRIMARY KEY CLUSTERED
(
[symbol] ASC,
[cDate] ASC,
[part] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

Stored procedure is:

USE [Analytical]
GO
/****** Object: StoredProcedure [dbo].[InsertDailyTickMinMaxAnalysis] Script Date: 08/07/2007 15:45:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[InsertDailyTickMinMaxAnalysis]
@symbol varchar(50),
@cDate varchar(50),
@part varchar(50),
@collection varchar(50) = NULL
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.DailyTickMinMaxAnalysis (
symbol,
cDate,
part,
[collection])
Values (
@symbol,
@cDate,
@part,
@collection)
END

C# procedure:
public void saveHistTickMinMaxAnalysis ( int tableIndex )
{
if ( tableIndex != -1 )
{
using ( SqlConnection conn_Analytical = new SqlConnection ( "server=ddmnvt61\sqlexpress;" +
"database=Analytical;Trusted_Connection=Yes" ) )
{
SqlCommand cmdm = new SqlCommand ( );
conn_Analytical.Open ( );
cmdm.Connection = conn_Analytical;
cmdm.CommandType = CommandType.StoredProcedure;
cmdm.CommandText = "InsertDailyTickMinMaxAnalysis";
cmdm.Parameters.Clear ( );
cmdm.Parameters.Add ( "@symbol", SqlDbType.VarChar, 50, "symbol" );
cmdm.Parameters.Add ( "@cDate", SqlDbType.VarChar, 50, "cDate" );
cmdm.Parameters.Add ( "@part", SqlDbType.VarChar, 50, "part" ); // 2
cmdm.Parameters.Add ( "@collection", SqlDbType.VarChar, 500, "collection" ); // 3
foreach ( DataRow row in Globals.dtSets.Tables[tableIndex].Rows )
{
cmdm.Parameters[0].Value = ( string )row[0];
cmdm.Parameters[1].Value = ( string )row[1];
cmdm.Parameters[2].Value = ( string )row[2];
cmdm.Parameters[3].Value = ( string )row[3];
Console.WriteLine ( "Saving {0}", ( string )row[3] ); // <= where the print out occurs to confirm that the right string is being passed
try
{
cmdm.ExecuteNonQuery ( );
}
catch { }
}
}
}
} // saveHistTickMinMaxAnalysis

Table is defined with the last columns[3].Length = 500;

What happened to the rest of my strings?

I would appreciate any help.

Many thanks.

View 3 Replies View Related

Error: String Must Be Exactly One Character Long

Aug 27, 2007

hi all, i'm retreiving user input using textboxes and saving to a gridview. i'm getting this error and i dont know whats causing it. <asp:SqlDataSource ID="SqlDataSource1" runat="server"
InsertCommand="INSERT INTO test101(Surname,Names,Regno)VALUES (@Surname, @Names, @Regno)"ConnectionString="<%$ ConnectionStrings:engineeringConnectionString %>" ProviderName=System.Data.SqlClient
ConflictDetection="CompareAllValues" >
 
<InsertParameters>
<asp:ControlParameter ControlID="TextBox1" DefaultValue="TextBox1.Text" Name="Surname"
PropertyName="Text" Size="50" Type=Char />
<asp:ControlParameter ControlID="TextBox2" DefaultValue="TextBox2.Text" Name="Names"
PropertyName="Text" Size="50" Type=Char />
<asp:Parameter DefaultValue="TextBox3.Text" Name="Regno" />
</InsertParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1"
runat="server" AutoGenerateColumns="False" AutoGenerateDeleteButton="True" DataKeyNames="ID"AutoGenerateEditButton="True" AllowSorting="True" BackColor="LightGoldenrodYellow"
BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None" PageSize="20"
Height="374px" EmptyDataText="null" DataSourceID=SqlDataSource1 Visible="False" >
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True"
SortExpression="ID" />
<asp:BoundField DataField="Surname" HeaderText="Surname" SortExpression="Surname" />
<asp:BoundField DataField="Names" HeaderText="Names" SortExpression="Names" />
<asp:BoundField DataField="Registration" HeaderText="Registration" SortExpression="Registration" />
<asp:BoundField DataField="Grade" HeaderText="Grade" SortExpression="Grade" />
 
</Columns>
</asp:GridView>
and the code behind is:protected void Page_Load(object sender, EventArgs e)
{SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=engineering; Integrated Security=True");
 
conn.Open();
 
 
GridView1.DataBind();
conn.Close();
 
}
 public void login1_Click(object sender, EventArgs e)
{
 
SqlDataSource1.Insert();
 
Response.Write("you have successfully being added to the database");
 can anyone help?!!!

View 1 Replies View Related

Too Long Input String Problem.

Sep 12, 2007

Hello Everybody,I have a problem that I have an exception from a SQLDataSource when a user types in more characters to a TextBox then is allowed.First of all - is there ant built in ASP.NET way to handle that problem?Second - if not then I have to handle the Exception from the SQLDataSource myself. I can write a handler for that which will look like:1 protected void DetailsViewDS_OnUpdated(object sender, SqlDataSourceStatusEventArgs e)
2 {
3 if (e.Exception != null)
4 {
5 // do some stuff
6 }
7 }
 The problem is that I would like to show the user an error message which says how many characters he can insert. So the other question is: is there any way to get automatically from the SQL DB what is a maximum length of a varchar? If I use LEN/LENGTH it gives me the current length of given entry (which actually is the current length of the varchar). But I want to get the maximum length of this concrete varchar (NOT the varchar(max) - this shows maximum length of a varchar in general - like ~20000 ).I hope I did not make it too complex ;)All the best,Joseph A. Habdank 

View 6 Replies View Related

Removing Rows That Contains Long String

Mar 23, 2004

Anyone know how to check the length of a varchar data in a table? I want to delete rows that contains an entry that exceeds 100 characters.

I should be able to write a program to do it, but I am wondering if I can do it in SQL.

View 2 Replies View Related

Inserting Long String Cuts Of At 150 Chars

May 18, 2006

Hi there, I'm having a problem when I insert a string (from C#) which is 167 characters long. The field in SQL Server Express is a varchar(250), but the string gets cut of somehow at 150 characters.

Why does this happen?

The table and stored proc I use are defined like this:

USE [Test]
GO
/****** Object: Table [dbo].[tblImage] Script Date: 05/18/2006 10:42:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblImage](
[imgID] [bigint] IDENTITY(1,1) NOT NULL,
[persID] [bigint] NOT NULL,
[imgPad] [varchar](250) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF



ALTER PROCEDURE [dbo].[spInsertImage]

@persID BIGINT

, @imgPad VARCHAR(150)





AS

BEGIN

SET NOCOUNT ON;

INSERT INTO tblImage ( persID

, imgPad )

VALUES ( @persID

, @imgPad)

END

Does anybody know what is going on?

Thanks,

Starik

View 3 Replies View Related

Find How Long Items Open Using Date Columns

Mar 11, 2013

I have a sample view with some dates. How would you find the numbers of items open per month. Say between OpenDate and CloseDate I want to find how many were open for January, February,?

Here is a sample table with the data

Code:
CREATE TABLE [dbo].[TestDate](
[ItemTitle] [nvarchar](50) NULL,
[ItemAttachAssignDate] [date] NULL,
[ItemDetachConcludeDate] [date] NULL,
[Status] [nvarchar](50) NULL,
[FullName] [nvarchar](100) NULL,
[OpenDate] [date] NULL,
[CloseDate] [date] NULL
) ON [PRIMARY]
GO

Code:
INSERT INTO [TestDate]([ItemAttachAssignDate], [ItemDetachConcludeDate], [Status], [FullName], [OpenDate], [CloseDate])
VALUES('2013-02-18 00:00:00', '2013-02-19 00:00:00', 'Done', 'Jeff Hunter ', '2013-02-18 00:00:00', '2013-02-19 00:00:00');
INSERT INTO [TestDate]([ItemAttachAssignDate], [ItemDetachConcludeDate], [Status], [FullName], [OpenDate], [CloseDate])
VALUES('2012-10-15 00:00:00', '2013-02-05 00:00:00', 'Done', 'Tommy Johnson', '2013-01-22 00:00:00', '2013-01-28 00:00:00');

[Code] .....

View 2 Replies View Related

(long) Querry Doesn't Fit In Query-string Window

Jun 4, 2007

hi,

i have worked three days on a query to display all my results in a beautiful report. The query is fine because when i execute it in Query Analyzer i have all results i want to see in my statistics-table in my report...

One thing: it's contains about 100 unioned statements, which results in a super-long query. Performance is OK because it are all 100 very easy statements that are union-ed together.

But, when I copy-paste it in my query-string window/textbox of the report designer, I see that there's a maximum on that textbox lenght, which results in the fact that my long query suddenly stops.

Any solutions????

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

Do Not Display The Result Of My Long Text String, Approx About 400 Characters

Mar 26, 2004

I have the following problem. My SQL Query that i wrote works but the result that is displayed in Query analyzer cuts most of my long text that I want in my result. The long text string is approx about 400 characters and the type is varchar of the field. Any ideas??

SELECT '510', PRODCLASSID
, '1', COMPONENTID,'ENG'+SPACE(2),'#'+SPACE(254),'#'+SPAC E(254),'#'+SPACE(254),'#'+SPACE(99),externalid,
'Desc1' = CASE
WHEN SUBSTRING(externalid,1,2) = 'MF'
THEN 'Full machine warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'MP'
THEN 'Full machine warranty, parts only : parts covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'PF'
THEN 'Power line warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'PP'
THEN 'Power line warranty, parts only : parts are covered at warranty rates applicable at the time of repair. '
END
+
CASE
WHEN SUBSTRING(externalid,LEN(externalid)- 3,4) = '2018'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 18 month or 2000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '3024'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 24 month or 3000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '4030'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 30 month or 4000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '5036'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 36 month or 5000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '6042'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 42 month or 6000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '8054'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 54 month or 8000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '1074'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 74 month or 10000 HRS, whichever comes first. '
END
+ 'Flexible warranty is handled according to the procedures described in ESPPM 3-10.'
+
CASE

WHEN prodclassid IN ('P1','P11','P8','P9')
THEN ' (mileage limited to 300 km)'
WHEN prodclassid IN ('P7')
THEN ' (mileage limited to 200 km)'
ELSE NULL
END
+
SPACE(5000 - LEN('Desc1'))
.......

View 1 Replies View Related

Transact SQL :: Check 2 Columns And Return Rows As Long As One Column Is Not Null

Oct 19, 2015

how to do a check for 2 columns. As long as there is data for at least one of the columns I want to return rows.

Example Data

create table test
(
ID int,
set1 varchar(50),
set2 varchar(50),

[code]....

View 4 Replies View Related

Text Doesn't Support Rtrim,ltrim Etc Functions...which Is Other Better Way To Save A Long String

Nov 10, 2007



Hi

I sent a long string of ID from front end to my stored procedure...till now I was using varchar(8000)...but if the string crossess that limit it is breaking.

If I try to use text datatype..It doesn't support rtrim, stuff functions etc...

So could any one suggest me a best way to save a long string without any restriction of size...

My front end is C#.Net and Back End is SQL SERVER 2000

Thanks in advance

View 1 Replies View Related

How To Send Long String Using SqlContext.pipe.send(strString)?

Aug 15, 2006

Hi,



I have a string almost 11006 length.. when i try to send back from SQLCLR procedure

it says cannot send ..

here is Exception Text "Message length 11060 exceeds maximum length supported of 4000."

Max limit to send a string using pipe is 4000

How I can send a string which is large in size than 4000.



Thanks

View 6 Replies View Related

Splitting Up One String Into Three Columns

Aug 14, 2003

I'm trying to split a hyphen-delimited string into three columns in a view. I've been using substring and len to split up the string, but it is getting very complicated (and isn't working in all cases). I've used a SPLIT function in vbscript - does t-sql have anything similar? I've attached a spreadsheet that shows what I am looking for. Maybe someone can guide me in the right direction?

Thanks.

View 3 Replies View Related







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