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


ADVERTISEMENT

T-SQL (SS2K8) :: How To Cut Certain Values In A String To Separate Columns

Jun 5, 2014

I have a column containing values for different languages. I want to cut out the values per languate in a seperat column.

The syntax is a 2 letter country code followed by : the value is contained in double quotes. each languate is separated by a ; (except for the last one)

EX ur English, Dutch and Swedish:US:"Project/Prescription sale";NL:"Project/specificatie";SW:"Objektsförsäljning"

The result would Be
column header US
with value Project/Prescription sale

next column header NL
with value Project/specificatie etc.

Here are table examples:

IF OBJECT_ID('[#SALETYPE]','U') IS NOT NULL
DROP TABLE [#SALETYPE]

CREATE TABLE [#SALETYPE](
[SaleType_Id] [int] NOT NULL,
[name] [nvarchar](239) NOT NULL,

[Code] ....

View 9 Replies View Related

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

Add 2 Separate Columns From Separate Tables Using Trigger

Feb 15, 2012

I am trying to add 2 separate columns from separate tables i.e column1 should be added to column 2 when inserted and I want to use a trigger but i don't know the syntax to use...

View 14 Replies View Related

SQL Server 2008 :: Store Long Values To Be Used In (IN) Statement In Separate Table?

Jul 14, 2015

I have several reports that are looking for a code within a certain set of codes or ranges. The specific list of codes to be including is determined by the end user. Currently my "IN" statement can be a hundred lines, listing several ranges, lists of specific codes, etc. I am constantly getting asked what codes does it include, is this code included, etc. Sometimes they'll give me a printed 10 page list of codes and want me to compare to what I have included in the report. Not ideal in the slightest.

What I'd like to do is have a table or a file of some kind somewhere where the end user can view the codes contained, add new ones, and delete ones they no longer want. Then I'd like to be able to just reference that file in my IN statement. Leaving the responsibility of listing the correct codes on them.

View 9 Replies View Related

Separate Columns

Nov 9, 2005

Hi to all of you,

I am new to SQL I have a problem that I can’t solve.
I have a column with Surname and name (SMITH, James) in one table with data I just need to separate in two columns in one Surname in the other one Name I know how to unite two columns using substrings but not to separate in two columns.

Help is highly appreciated

View 4 Replies View Related

Separate String For Each Row By & Character

Oct 13, 2015

I simply need to separate the string for each row by the & character and then I'm assuming i ll be able to COUNT and GROUP BY the occurrences of each separate value in order to find the most commonly used inputs.I have a column Variables in the table Functions, that contains a string of values separated by the & character that shows the inputs each student inserted into a function.

How would I go about splitting that string without the use of a function or stored procedure and the find the most commonly used variables? (I was thinking the latter part could be easily solved with a COUNT(*) and appropriate GROUP BY.)

Example of data:

StudentID FunctionName Variables

1 Example1 Var1=10&Var2=xy&Sign=True&Role=False

View 2 Replies View Related

Separate String To Three Column

Mar 2, 2015

How to separate column FullName to three column LastName, FirstName, and MI? Sample of FullName - Smith, John P.

View 4 Replies View Related

How To Do Separate The Concatenated String

Oct 5, 2007

declare @filter varchar(100)
set @filter = '10,''firststring''||10,''secondstring'''
declare @tbl table
(id decimal,
name varchar(20))

insert into @tbl values (substring(@filter,0,patindex('%||%',@filter)))


hai in the above exmaple, i recieve input value (@filter) as concated string . pipeline(||) is my delimiter..
i want to split the string based on this delimater and need to insert into @tbl..

There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.


What is the error in this. i believe i can do this way to insert to concatinated values.
Help pls

View 6 Replies View Related

SQL 2012 :: Split CSV Records To Separate Columns

Jun 12, 2014

I've a table as below

custid,companyname,phone,address
2,AAAAA,(222) 222-2222,address 2
3,cust 3,(333) 333-3333,address 3
5,BBBBB,(333) 333-3333,DDDDD
6,cust 6,(222) 222-2222,address 6
7,cust 7,(222) 222-2222,address 7

How to split csv values to new fields. so that the desired output should be as below

custidcompanynamephone address
2 AAAAA (222) 222-2222 address 2
3 cust 3 (333) 333-3333 address 3
5 BBBBB (333) 333-3333 DDDDD
6 cust 6 (222) 222-2222 address 6
7 cust 7 (222) 222-2222 address 7

View 9 Replies View Related

Determine Value Based On Two Separate Date Columns

Jun 20, 2014

This is the logic I need to incorporate in to sql

if the getdate() < term start date then R
if the getdate() > term date date and getdate() < term end date then C
if the getdate() > term end date then H

I have come up with the following type of case statement that will allow me to determine the first two values (to a degree), however , I need to evaluate the end date as well.

Is there a way to look at multiple columns in a case statement?

CASE
WHEN DATEDIFF(DD,GETDATE(),TRM_BEGIN_DTE) >0 THEN 'R' else 'C' end

View 5 Replies View Related

SQL 2012 :: Results To Show Up As Separate Columns

Aug 22, 2014

We have the below query that pulls benefit ids for employees but it will show each benefit on a separate row but we would like to have just one rows for the employee and columns for each of the benefits.

SELECT
hcd.PersonId,
hcd.PlanYear,
hcd.TaxIdNumber,
hcd.LastName,
hcd.FirstName,
hcd.BirthDate,

[code]....

View 3 Replies View Related

SQL 2012 :: Put Results Into Separate Columns Based On Value?

Feb 25, 2015

I am trying to take the results of a query and re-orient them into separate columns.

select distinct
W_SUMMARYDETAILS.FACILITY_ID,
W_SUMMARYDETAILS.REPORTING_YEAR, (2011 - 2014, I want these years broken out into columns for each year)
W_SUMMARYDETAILS.FACILITY_NAME,
W_DEF_SUMMARYDETAILS.REPORTING_PERIOD (2011 - 2013, I want these years broken out into columns for each year)
From W_SUMMARYDETAILS
full outer join W_DEF_SUMMARYDETAILS
on W_SUMMARYDETAILS.FACILITY_ID=W_DEF_SUMMARYDETAILS.FACILITY_ID and
W_SUMMARYDETAILS.REPORTING_YEAR=W_DEF_SUMMARYDETAILS.REPORTING_PERIOD

As of now the query puts all the years into a single column -- one for DEF_SUMMARY and another for SUMMARY.

I am looking to create 7 additional columns for all the individual years in the results instead of just two columns.

View 9 Replies View Related

Calculating Percentages And Showing In Separate Columns

Aug 31, 2013

SELECT DISTINCT CASE WHEN SM.SERVICE_TYPE_N = 1 THEN 'LABORATORY'
WHEN SM.SERVICE_TYPE_N = 2 THEN 'PODIATRY'
WHEN SM.SERVICE_TYPE_N = 3 THEN 'ADMINISTRATION'
WHEN SM.SERVICE_TYPE_N = 4 THEN 'DIET'
WHEN SM.SERVICE_TYPE_N = 5 THEN 'DENTAL'

[Code] ....

In the above query i need to calculate 100%,30% and percentage other than 100 and 30 and show them in separate columns how to do that?

1)ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [100_PERCENT]
WHERE BM.BILL_AMOUNT_M=BM.CONCESSION_AMOUNT_M

2)ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [30_PERCENT]
WHERE AND BM.CONCESSION_AMOUNT_M=BM.BILL_AMOUNT_M * 0.30

3) ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [OTHER_CONCESSION_PERCENT]
WHERE BM.CONCESSION_AMOUNT_M between BM.BILL_AMOUNT_M*0.001 and BM.BILL_AMOUNT_M*0.299
OR BM.CONCESSION_AMOUNT_M between BM.BILL_AMOUNT_M*0.301 and BM.BILL_AMOUNT_M*0.999

View 3 Replies View Related

Returning Subsets Of The Same Column In Separate Columns...

Mar 23, 2006

I have the following 2 Sql queries. They both are rowcounts of the same column but based on different criteria. What I want to do is return the two results side by side in separate columns:
-- Subscriptions since Sept. 24th
SELECT count(*)
FROM SiteMemberTable103 s(nolock)
JOIN clientmembertable25 c(nolock) ON s.memberid = c.memberid
WHERE site_firstjoindate is not null
and c.clientunsubscribe = 0
and c.validemailaddr = 1
and s.unsubscribe = 0

-- Subscriptions in February
SELECT count(*)
FROM SiteMemberTable103 s(nolock)
JOIN clientmembertable25 c(nolock) ON s.memberid = c.memberid
WHERE site_firstjoindate BETWEEN '2006-02-01 00:00:00.000' AND '2006-03-01 00:00:00.000'
AND c.clientunsubscribe = 0
AND c.validemailaddr = 1
AND s.unsubscribe = 0

It seems like a UNION ALL should work but it just returns the results in one column. I tried changing the count by specifying a different column for each but that doesn't work either. I also tried writing it as one query and using alias to differentiate the two tables but that just gives me syntax errors. I suspect there is a more elegant way to do this but I'm at a loss. Any help would be greatly appreciated!

Caeanis

View 1 Replies View Related

Get Latest Records When Date And Time Are Separate Columns?

Mar 26, 2012

I have 2 tables:

TransactionsImport (which is the destination table)
TransactionsImportDelta

I need to do the following:

Get the records with the latest date and time in the destination table TransactionsImport
Get the records with the latest date and time in the destination table TransactionsImportDelta table
Insert the records from the TransactionsImportDelta table into TransactionsImport that have a greater date & time than the current records in TransactionsImport table.

Problem is date & time are in separate columns:

Table structure:

Date Time ID
2011121305154107142201008300100
2011121305154122B1L13ZY0000A07YD
2011121304504735142201090002600
2011121304504737142201095008300
2011121304504737142201090002600

View 2 Replies View Related

SQL 2012 :: Separate Domain Attribute Into Two Columns In Excel Add-In?

Aug 18, 2014

Can you separate a domain attribute into two columns in the Excel Add-In?

We are using the Excel Add-In as the UI for business users and they want to see one of the domain attributes as 2 different columns.

The attribute is Store, currently it is displayed like this:

"123 {STORENAME }".

They want it displayed in two columns in the Excel document for easier filtering/sorting: Store Number and Store Name.

Is this possible? If one column is changed the other would change right along with it.

View 2 Replies View Related

Data Mining :: Merge Two Or More Columns In Separate Tables

Jul 14, 2015

SQL 2008R2

Request is to merge or join or case stmt or union or... from up to four unique columns all in separate tables to new combined table (matrix) of results from said.

What is example of best method to do this ?

View 2 Replies View Related

SQL Server 2014 :: Splitting Similar Data Into Separate Columns?

Aug 18, 2015

If you see below there are 2 customer names on 1 loan, most of them share the same lastname and address, I want to separate it with fields,LoanID, customer 1 Firstname, Customer 1 Lastname, Customer 2 FirstName, Customer 2 Lastname, Adddress,zip

Loan IDFirst NameLastnameAddressaddress 2CityStateZip
1236048Joey Yesen xxxx abc GROVE RDNULLCLEVELANDTX77327
1236048Dickey Yesen xxxx abc GROVE RDNULLCLEVELANDTX77327
1235983Randy Seany xxxx abc Haleyville StNULLAuroraCO80018
1235983Barry Seanyxxxx abc Haleyville StNULLAuroraCO80018

The query I am using

select
L.Loanid
,B.FirstMiddleName
,B.LastName
,MA.AddressLine1
,MA.AddressLine2
,MA.City
,MA.State
,MA.Zip

from Loan AS L

LEFT JOIN Status As S on S.LoanID = L.LoanID
LEFT JOIN Borrower B on B.LoanID = L.LoanID
LEFT JOIN MailingAddress MA on MA.LoanID = L.LoanID
where S.PrimStat = '1' and B.Deceased = '0'

View 3 Replies View Related

Transact SQL :: How To Split Comma Separated Columns Into Separate Rows

Sep 14, 2015

I have values in two columns separated by commas, like shown below:

I need the Output Like

How to do this in SQL server ?

View 6 Replies View Related

Transact SQL :: Flat Text File - Separate HTML String

Jun 24, 2015

I have a flat text file with lots HTML tags and corresponding values 

For example 
<Near_Side> 5563 </Near_Side>
<Top_Down_Code> Xe345 <Top_Down_Code> 

So, For example I can use the a function 

ALTER function dbo.StripHTML( @text varchar(max) ) returns varchar(max) as
begin
    declare @textXML xml
    declare @result varchar(max)
    set @textXML = REPLACE( @text, '&', '' );

[Code] ...

To which Select dbo.StripHTML('<Near_Side> 5563 </Near_Side>')  Value 

I'll get 5563

However how would you get the values within the tag itself ? E.g. 

Value Name 
5563 Near_side
Xe345      Top_Down_Code

I was thinking along the Charindex but cant seem to get it right.

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

SQL Server 2012 :: Extracting Data From Row And Inserting Into Separate Columns In Different Tables?

Mar 19, 2015

From my query I am getting results like below in one of the column:

'immediate due 14,289.00
04/15/15 5,213.00
05/15/15 5,213.00
06/15/15 5,213.00
07/15/15 5,213.00
08/15/15 5,213.00
09/15/15 5,213.00
10/15/15 5,213.00
11/15/15 5,210.00'

this same type of many rows are there (i just mentioned one) but having same pattern with tabs as delimiter in between dates and amount.

I need something that shows Date on one side representing particular amount on the other

For Immediate Due it will be current date and the amount besides it.

how can I achieve this.

View 8 Replies View Related

In SSIS, What Is The Best Way To Take A Column With Comma Separated Strings And Separate Them To Multiple Columns

Jul 10, 2006

Hi There,

Can anybody suggest me what is the best way to take a column with comma separated stings and output them into multiple columns with strings?

for example if I have a column with "watertown, newton" as a string, I need to separate them to two columns with watertown and newton values?

Is Derived column transformation the best way to do it?

Thanks.

Sam.

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

SQL Server 2012 :: Select Query To XLS Output - Export Data In Columns To Separate Tabs In Excel

Apr 21, 2015

Using below script to export the select statement result to .xls

declare @sql varchar(8000)
select @sql = 'bcp "select * from Databases..Table" queryout c:bcpTom.xls -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql

But result is not exporting in seperate tabs, all 4 column details are exporting in single cell.

how to export the data in columns to separate tabs in excel.

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

Reducing Storage Space By Moving Rarely Required Columns To Separate Table While Sharing Primary Key Constraint

Aug 17, 2006

Dear all,

In my current database design, there is one table (PState) which has a Primary Key (int) and a few other fields.

During development, a pattern started to arise; for certain rows in PState, I wanted to specify an additional set of columns (over 10 of them with quite large lengths) for each row in PState. However, as these additional columns would only be required in approximately 20% of the rows of PState, there would be plenty of NULL values in PState if I would make this table wider than necessary. So, I decided to create a separate table with those optional columns (PStateWFI). In order to attach these additional columns in PStateWFI to PState in the cases they were needed, I would obviously have to create a Foreign Key constraint on the Primary Key of PStateWFI so that these optional rows would know which row in PState they would belong to.

However, the problem with this approach is that one could define multiple rows in PStateWFI referring to the same row in PState, which would not make sense. Thus, a UNIQUE index constraint added to the constrained ID column in PStateWFI would make sense to ensure that there could only be one set of optional columns added to each row in PState. But now, when adding the UNIQUE index, the FK constraint started to appear as a bidirectional key link in the Diagram; hence, new entries in PState would have to meet a FK constraint based on PStateWFI, which was not intended.

Hence, I had to create a quite awkward design to enforce the constraints:

1. The PState table has a Primary Key (PState.ParticleID, int, Identity Specification: Yes)
2. The PStateWFI table has a Primary Key (PstateWFIID, int, Identity Specification: Yes)
3. PStateWFI has field "PStateID" which has a FK constraint to PState.ParticleID (which is a one-way constraint operating in the correct way and does not constrain insertions in PState)
4. PStateWFI has an additional column ParticleIDIndex which has a UNIQUE Index attached to it.
5. There is a check constraint on PStateWFI enforcing PStateWFI.ParticleIDIndex = PStateWFI.ParticleID.

Although this structure does the job, it makes it necessary to add a redundant column in PStateWFI by duplicating the PStateWFI.ParticleID into PStateWFI.ParticleIDIndex, since I can't create a UNIQUE index on PStateWFI.ParticleID without constraining the PState table as well. So, insertions into this table would have to insert the same value into two columns. Not a big deal, but appears slightly ugly.

Basically I'd hope someone could explain why a bidirectional FK constraint has to be enforced on the primary key table in a relationship when the constrained column in the primary key table has a UNIQUE index attached on it. I have a few other cases where the above approach would benefit from a more clear structure.

Thanks in advance for any advice.

View 6 Replies View Related

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







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