Separating Text Into Separate Varchars

May 10, 2005

with varchars having a max of 8000 bytes and partly because of the coder before me, I'm stuck with using text. the text has a maximum of 30,000 characters, i've tried everything to try to separate the text into 4 different varchars to no avail.. none of the string functions (left, substring, right) works on a text field, and when i try to convert it to a varchar (i.e. convert(varchar(8000), @text)) it obviously only takes the first 8000 characters...

what can i do in this case? any help would be appreciated! thanks in advance!

View 2 Replies


ADVERTISEMENT

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

Large Amount Of Text Pushing To Separate Page After PDF Render

May 25, 2006

I am having a small problem.

When I generate a report with a large amount of text data (Paragraphs) the report will not split the text between two pages.

It will move the entire text box to the next page leaving a large amount of space on the previous page.

I tried every control on the page to render this. I tried textboxes, tables, lists, rectangles, subreports, etc..

The data is stored in a SQL table using a text data type (there can be a large amount of data entered into these database.

Any help on this would be great. I like reporting services, but there are just a few bugs.

I am using Reporting Services 2000 with Service Pack 2 and Hotfix

http://support.microsoft.com/?kbid=912424 (That fixed a different PDF rendering issue) and this problem was occuring prior to this hotfix.

Thanks in advance

View 5 Replies View Related

Manipulating Varchars As Datetime

Aug 10, 2007

Hi all,
I am a little weak at SQL, so bear with me.
Can anyone give me an SP which uses "Between" to Display all Dates
BETWEEN a fromDate and toDate???
Two conditions
1.Both Dates are stored as varchar.
2. I should be able to get the dates even if years are different, say between 20th December 2006 and 3rd January 2007
3.Is there a way to extract the yearDiff? 
Regards,
Naveen

View 2 Replies View Related

Using User Defined Type Instead Of Varchars

Jul 20, 2005

A common request for enhancement to applications is to "make thisfield bigger". I know I've been caught with increasing a field size,and then spending hours debugging because another stored proc has avariable or temp table that uses the field defined as the originalvarchar size. SQL Server just truncates to fit the data into thesmaller varchar, and so there are no errors raised.An option suggested by a colleague is to no longer use varchars, butuse User Defined Types instead. To make this work effectively,though, they suggest we would need to make it a rule that we do notuse varchars anywhere except to define user defined types.Though there will be one point of changes I can't help thinking thisisn't a very good idea ! Any thoughts ?Thanks.

View 2 Replies View Related

Implicit Conversion Error - Adding Different Varchars Together

Aug 27, 2005

Hi I get the following error:

Implicit conversion of varchar value to varchar cannot be done
because the collation of the value is unresoved due to a collation
conflict


SELECT
    Accounts_Users.NameFirst + ' '
+ Accounts_Users.NameLast + ' (' + Accounts_Users.BusinessUnit + ')' AS
NameFull,  -- Error here
--    Accounts_Users.NameFirst + ' ' + Accounts_Users.NameLast AS NameFull,  -- This line works
    Accounts_Users.EmailAddress, Accounts_Users.BusinessUnit
    FROM Accounts_Users.Accounts_Users


NameFirst and NameLast are VarChar(30) and BusinessUnit VarChar(50), I
did try converting it to 30 also but still got the same error

The sp works on my dev sql server but not on the live one, I am using Management Console v2 (dev) and 1.2 (live server)

Thanks

Steve

View 2 Replies View Related

Visual Interdev Truncates Long Varchars At 255

Sep 28, 1999

We have an application that uses ASP pages to get data from a SQL 7.0 database. One of the stored procedures that is called brings back a comment field that is a varchar(2000). For some reason, when this SP is called from the ASP page, the field is truncated at 255 characters. If the SP is run in the Query Analyzer, all 2000 characters are brought back (after we reset the default length in QA).

Is there any way for the ASP page to bring back more than 255 characters?

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

Display Output On Separate Separate Line

Feb 10, 2007

How can i format my query so that each piece of data appears on a new separate line? Is there a command for a new line feed? does not work.

thanks.

For example:

a: data
b: data
c: data

a: data
b: data
c: data

View 6 Replies View Related

Separating Address Column

Sep 18, 2014

I have a address column in one of my databases that I need to split out by carriadge return but don't know what to start..This is my example query

SELECT
Address
FROM
PUB."Address"
Where "Id" = 123456

3 The StreetMayfairLondonEngland...I would like the results to show

Either

3 The Street
Mayfair
London
England

Or the same but horizontal

View 1 Replies View Related

SQL 2012 :: Separating Results From 1 Column?

May 13, 2015

i have written a query I was SO proud of but THEN was told to separate the values out of one column. simple way to do this without having to rewrite the whole query? Will I need to include a Case statement?

Basically the column contains several types of vacation codes which will need to be renamed to 2. So there is leave 1, leave 2, and leave 3 and sick. I will need to rename all leave 1, 2, and 3 to just vacation and leave sick as sick.

View 3 Replies View Related

Substring - Separating To 5 Additional Columns

Jun 6, 2014

I have data as below:

columnA
D7 330 4/13/2014 0:0:0 KUL PVG 4/13/2014 18:35:0 4/13/2014 19:15:0 4/13/2014 23:45:0 4/14/2014 0:45:0

How can I separate it to 5 additional columns?

columnB: D7 330 4/13/2014 0:0:0
columnC: KUL PVG 4/13/2014 18:35:0
columnD: 4/13/2014 19:15:0
columnE: 4/13/2014 23:45:0
columnF: 4/14/2014 0:45:0

View 7 Replies View Related

Transact SQL :: Separating String From URLs

Jun 9, 2015

I have column with location for all reports, dashboards, images, pages in URL format from Sharepoint audit database. Like

sites/fm/finance/PowerPivot Gallery/FinancialStatement.rdlx
sites/ea/Mn_medical/Pages/Medical_DP/3 Site Compare.aspx

There are millions of rows like the above URLs. I need to separate all the strings from that ULR separated by "/" and make column for each so that I can create a hierarchy in tabular model. How do I write a sql for that. I could get first one and last ones with these kinds of substring:

SUBSTRING( b.DocLocation , LEN(b.DocLocation) -  CHARINDEX('/',REVERSE(b.DocLocation)) + 2  , LEN(b.DocLocation)  )

This gives me last piece of ULR. How I do I get all the middle pieces of URL?

View 12 Replies View Related

Separating Desired From Undesired Duplicate Records

Nov 20, 2007

I know of several methods to remove duplicate records but I recently encountered a unique situation where some duplicate records were actually acceptable.

Here is my situation:

I have a table that contains records of individuals who have children so 1 person can have 3 children with different birthdates; but there is also a field that has a specified language. The challenge arises when an individual may have only 1 child but has entered a single record twice. Once with a specifed language and again without which produces a default value of UNKNOWN.

I need to be able to remove this record without affecting records that may have a record entered twice as well; having 2 children for instance but they may also have a specified language in one record but a default value of UNKNOWN for their second record.

So I can't eliminate the unwanted duplicates by filtering out records that have UNKNOWN because I would also remove individuals that I need.

EX:
firstN | lastN | address | lang | childs birthdate
John Doe 210 Somewhere Ave ENG 1993-10-09
John Doe 210 Somewhere Ave UNK 1993-10-09
Jane Doe 210 Anywhere Ave ENG 1969-12-23
Jane Doe 210 Anywhere Ave UNK 1958-04-15

How could you remove the duplicate for John in this example without affecting Janes duplicate record which is actually ok because she apparently has 2 children with different birthdates whereas John's duplicate record is obviously created because it was entered twice; once without entering
a language and the second time specifying the language?

I have tried a number of things short of creating a cursor which isn't really the best way to resolve this issue since there are millions of rows.

Anyone out there have any input that be helpful? Or has anyone ever had this similar issue? I would be interested in knowing how you addressed the problem.

Any help is appreciated. :)

View 2 Replies View Related

T-SQL (SS2K8) :: Separating Values - Number And Alphabets

Jul 28, 2015

Below are the same data

with Sample(Size) as (
select '16.3 Oz.' union all
'1' as union all
'2 Tablespoons' union all
'46. Oz. Each' )

Iwant to separate number and alphabets. But i wanted to keep the dot to have decimal values. Expected output

select '16.3' as val1 'Oz' as val2 union all
'1' val1 as union all
'2' as val1 'Tablespoons' as val2 union all
'46' as val1 'Oz Each' as val2

Please note that i need to remove the extra dots at the end of the the val1 and no dots in val2

I rid some of the functions like dbo.fn_StripCharacters and dbo.fn_GetAlphabetsOnly ffound in the internet. evey with my own logic. i couldn't remove the dot which appear at the end of val1

View 5 Replies View Related

Transact SQL :: How To Update New Table By Separating Single Date Column

Oct 11, 2013

I am trying to build a DIM table using a source table that has the following setup...

CREATE TABLE [dbo].[APPL_STATUSES](
[APPLICATIONS_ID] [varchar](10) NOT NULL,
[POS] [decimal](10, 0) NOT NULL,
[APPL_STATUS] [varchar](5) NULL,
[APPL_STATUS_DATE] [datetime] NULL,
[APPL_APPLICANT] [varchar](10) NULL)
GO

[code]....

What I am trying to do is to break out the APPL_STATUS_DATE into a STATUS_START_DATE and an STATUS_END_DATE in a new table.  I also need to be able to update the STATUS_END_DATE based on the previous day's date. Like so...

CREATE TABLE [dbo].[APPL_APPLICANT_STATUSES](
[APPLICATIONS_ID] [varchar](10) NOT NULL,
[POS] [decimal](10, 0) NOT NULL,
[APPL_STATUS] [varchar](5) NULL,
[STATUS_START_DATE] [datetime] NULL,
[STATUS_END_DATE] [datetime] NULL,
[APPL_APPLICANT] [varchar](10) NULL)
GO

[code]...

View 10 Replies View Related

Separating IIS6/asp.net Server From WS2K/MSSQL Server

Nov 30, 2006

Hi folks. I am sure this is a common situation so there shouldn't be any problems ;-) I need to run a web server and a separate database server, but am having trouble working out what username I can use in my SQL database. Here is my setup:Web server: [Windows Server 2003, IIS6, ASP.NET 1.1] The anonymous web account for the website has been changed to one created by me, so I know the password - lets call it 'IUSR_ME'. Basides that it is all default. IIS_WPG has also been given access to all website files. Database Server: [Windows Server 2000, SQL Server Standard Edition] I already have a 'classic' ASP application running on this setup, and this was achieved by putting IUSR_ME as a user in the database. Does anyone know how to do it with ASP.NET? Many thanks in advance.George 

View 1 Replies View Related

Separating One Field Into Two Fields Based On A Character In The Field

Jul 20, 2005

I know there has to be a way to do this, but I've gone brain dead. Thescenario..a varchar field in a table contains a date range (i.e. June 1,2004 - June 15, 2004 or September 1, 2004 - September 30, 2004 or...). Theusers have decided thats a bad way to do this (!) so they want to split thatfield into two new fields. Everything before the space/dash ( -) goes intoa 'FromDate' field, everything after the dash/space goes into the 'ToDate'field. I've played around with STRING commands, but haven't stumbled on ityet. Any help at all would be appreciated! DTS?

View 1 Replies View Related

SSIS Newbie - Separating SSIS &&amp; DB Engine

Oct 10, 2007

Hi Gurus,

I am new to SSIS. I am trying to install just the SSIS in one machine("SSIS Machine") and just the DB Engine ("SQL Server Machine") in another machine. What I am trying to do is, separating the SSIS service and packages from the Database Engine and trying to run in in another machine. I have few questions on this topic. I searched on this forum but I couldn't find a concrete answer to those questions. Forgive me if it already answered/asked multiple times.

1. When I install SSIS in "SSIS Machine", do I need to install client components also in the same machine?
2. I already established this setup (SSIS with client components in one machine and SQL Server in another) but when I tried to connect to the SSIS thro' Management Studio from Sql Server machine, I keep getting "Access Denied" error. Is it possible to connect to SSIS server from another machine (using Management studio)? I tried the DCOM security permission options I found in the internet(I don't have domain id so I gave "Everyone" full access) but still I get the same error. Any help would be appriciated.
3. Do I need 2 SQL Server Licenses (Enterprise) if I go with this environment?
4. Is it possible to configure SQL Job to run SSIS installed in another machine?

I would appriciate any help on this issue.

Thanks

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

Aug 18, 2005

Hi guys,

How can I separate the data that is alpha numeric in type?


FloorNumber

8A
8B
8C
11A
11B
12
13
14
15A


I need to separate the 8 from A in 2 columns just like ....

UnitLevel UnitCode
8 A
8 B

And if the floor numbers do not have "letters" then let it be.

Thanks.

View 2 Replies View Related

Separate A Column Into Many

Jul 19, 2007

Hi

I have a column say “Col_1� in which “=>� is used as separator.
Col_1 data is as follows
House => Street => Area => City => Country =>

I want to create separate columns for House, Street, Area etc from Col_1 using “=>’ separator.

Please advise how?

Thanks
Jawad

View 1 Replies View Related

Separate An And/Or Parameter

Jul 23, 2005

I want a user to be able to search for names in a table of clientsusing a single parameter, but be eable to use "and" or "or" IN theparameter.Like this:@NameSearch nvarchar(100)ASDECLARE @PartA nvarchar(100), @PartB nvarchar(100)CASE When @nameSearch Like '%' + ' OR ' + '%'SELECT @PartA =(how do I get what's to the left of "OR" or "AND")SELECT @PartB (how do I get what's to the right of "OR" or "AND")SELECTc.UniqueID,c.LastNameFROMdbo.tblClients cWHEREc.LastName = @PartAORc.LastName = @PartBCASE ELSESELECTc.UniqueID,c.LastNameFROMdbo.tblClients cWHEREc.LastName = @NameSearch

View 14 Replies View Related

64-bit Separate Install??

Jul 24, 2007

I want to know if 64-bit SQL Server 2005 is a separate install. I have been provided with SQL Server 2005 enterprise CD. I tried browsing through the folder but could not find 64-bit install.



I am assuming the CD I have is 32-bit because the system configuration check report had this message "64-bit ASP.NET is registered. Required 32-bit ASP.NET to install Microsoft Reporting service 2005(32 bit)"



The windows server I am trying to install is Windows Server 2003 Standard x64 Edition. I uninstalled the all the client and SQL Server 2005 install. Since my windows server 2003 hardware requirements are compatible for 64-bit SQL server 2005, I want to go ahead and install 64-bit SQL server 2005.



Any help will be greatly appreciated.

View 8 Replies View Related

Inner Join On Two Separate Databases

May 30, 2008

I need to do a inner join on tables from two separate databases.  I understand that you can do this by using this type of syntax:
select a.col1, b.col2from db1.dbo.tab1 a, db2.dbo.tab2 bwhere a.col1 = b.col2however, how do I reference the two databases in the following code?
Thanks,
Tim
 
Function GetConnectionString() As StringDim ConnectionString As String = ConfigurationManager.ConnectionStrings("MainWeb").ConnectionString
Return ConnectionString
End Function
 Using conn As New SqlConnection(GetConnectionString())
conn.Open()
Dim sql As String
sql = "SELECT CaskInfo.CaskID, CoCInfo.CoCName, AmendmentInfo.AmendmentName FROM CaskInfo INNER JOIN CoCInfo ON CoCInfo.CoCID = CaskInfo.CoC INNER JOIN AmendmentInfo ON AmendmentInfo.AmendmentID = CaskInfo.Amendment WHERE "For i = 0 To UBound(words)
If i > 0 Then sql = sql + " OR "
sql = sql + "(CoCInfo.CoCName + ' ' + AmendmentInfo.AmendmentName) LIKE '%" + words(i) + "%'"
Next
' lblResults.text = sql' Exit Sub
Dim com As SqlCommand = New SqlCommand(sql, conn)
Dim result As SqlDataReader = com.ExecuteReader()
Dim SearchResults As StringWhile result.Read()
SearchResults = SearchResults + result.GetInt32(0).ToString + " " + result.GetString(1) + " " + result.GetString(2) + "<br>"
End While
result.Close()
lblResults.text = SearchResults
conn.Close()
End Using

View 5 Replies View Related

Combined 2 Data And Separate Them

May 26, 2005

I have a data grid with dropdownlist.the dropdownlist is populated with datas wth a sql statement with 2 combined datamy sql : SELECT NAME + CAST(ID as CHAR(10)) FROM TABLE1When i select a value from the dropdownlist, i need to separate the data, name and id into different columnshow do i do it?Is there a way to manipulate the sql to do such a thing?

View 1 Replies View Related

Select From 2 Separate Databases

Jul 21, 2000

How do you select data from 2 separate databases residing on 2 servers?

Is there a way?

Angel

View 1 Replies View Related

Sql 7 Clustering - Separate Transaction Log

Feb 8, 2001

Has anyone implemented SQL 7 on a cluster(NT 4 Enterprise). It has been running for about 1.5 years with no problems. However, I now wish to manage the transaction log on a separate disk to the data, to increase performance.
I think I would need to create a new Disk resource on the cluster for the transaction log. But am not sure of the consequences during failover.

Has anyone done this, so that the disks containing the transaction log also failover.
There are some articles at the microsoft site, but none deal with managing the transaction log separately on a Cluster.

View 4 Replies View Related

Running NT & SQL 7 On A Separate Drive

Nov 2, 1999

I want to keep Win98 and my files intact on my C drive then install another hard drive and install MS Back Office (NT 4.0, SQL 7.0) on the new drive. When I want to switch operating systems, I want to restart in dos, go to the new drive and launch NT. I can't dual boot because I have FAT32 file system on my C drive. Has anybody tried this? Do it work?

View 1 Replies View Related

Trigger Between Two Tables In Separate Db

Mar 3, 2001

I am new to T-SQL and triggers
Any help will be appreciated

I am trying to change this code to insert firstname, surname (taken from employee table on db A) to firstname, surname on customer table of DB B but also create cust_id on customer table and DB B. currently I am getting all rows of customer.cust_id filled with the same data whenever a new data is inserted into (firstname,surmname of employee table)

Create trigger gen_cust_id ON employee for insert
AS
Update customer SET cust_id =( SELECT
Replicate('0',(4-DATALENGTH(CONVERT(varchar(10),i.id))))
+ Convert (varchar(10),i.id)
+ Substring(i.lastname,1,3)
+ Substring(i.firstname,1,1)
from employee C INNER JOIN inserted i on i.id=c.id)
from employee C INNER JOIN inserted i on i.id=c.id

Saad

View 2 Replies View Related

Separate Fields Into Rows

Sep 18, 2006

I have a table with accountid and labtype
However inside of the labtype is data separated by semicolons like below

GIV;APS;LSL. What would be the best way to return this data as multiple rows such as

acctid lab
100 GIV
100 APS
100 LSL

Thee may be from 1-10 values in the labtype field

View 1 Replies View Related

Separate Data In One Field

Feb 28, 2002

I recieved a SQL Server table that was supposed to have just the firstname in a field, but actually has firstname and middle name.
Example David Michael
Carol Anne
Is there a way in a query to look for the blank space and separate the names?

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







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