I have a field which contains something like prj(5616) .
I have been assigned to display the actual name and not the text with the number.
Example: if prj(8616) is called Soccer , then I want display Soccer instead of prj(8616).
Hello everyone !
I want to perform Full Text Search with SQL Server 2000. My documents (.doc, .xls, .txt, .pdf) are stored in a SQL Server field which is binary (the type of the column is image).
I would like to know, how you can extract pieces of text from the documents.
Example:
I have a ASPX page with codebehind in C# making the search in a table in SQL server that is full text indexed.
I make a search looking for the word "peace", than SQL server will take care about the search and return it to me the rows that match with that. But also I'd like to extract the 50 characters before and after where sql server found the word "peace" to show in the result page.
Does anyone has any idea how to work around it ?
Best regards.
Yannick
Yeah, it's pretty simple. Maybe it'll help someone out.
-- USAGE: fn_extract_chars(string_to_search, 'letters' -or- 'numbers')
CREATE FUNCTION fn_extract_chars (@x varchar(128), @y char(7))
RETURNS varchar(128)
AS
BEGIN
DECLARE @chars varchar(128)
DECLARE @pos int
DECLARE @action varchar(32)
SET @pos = 0
SET @chars = ''
IF @y = 'numbers' SET @action = '[0-9]'
ELSE IF @y = 'letters' SET @action = '[a-zA-Z]'
WHILE @pos < (DATALENGTH(@x) + 1)
BEGIN
IF PATINDEX(@action,SUBSTRING(@x, @pos, 1)) > 0
BEGIN
SET @chars = @chars + (SELECT SUBSTRING(@x, @pos, 1))
END
SET @pos = @pos + 1
END
RETURN(@chars)
END
I have two tables in SQL Server--an expression table and a translate table.
DECLARE @EXPRTABLE TABLE
(
expression varchar(100)
)
INSERT INTO @EXPRTABLE
SELECT 'CALL("-","","TEST")'
[Code] ....
I am trying to (for lack of a better way to explain it), overlay the pattern onto the expression and extract the text where the "#" symbols are.
So,
CALL("-","","TEST") using pattern CALL("#","#","#") would return -,,TEST
SUB(2,67,TEST,4,) using pattern SUB(2,#,TEST,4,) would return 67
View 1 Replies View Related
I have a 2table like
create table test1
(col1 identity(1,1),
col2 varchar(20)
)
create table test2
(old int,
new int)
Existing record in test1 table
(1,'test')
Now I want to insert a new record into test1 and then capture the old and new identity columsn in test 2 table
i.e. after insert test1 table will be
1,test
2,test
and test2 table will be like
1,2 -- old and new identity columns are captured during insert and stored in test2 table.
If the repeat the process again test2 table will be like
1, test
2,test
3, test
and test2 table will be
2,3 --- test2 table will always have only one record. old records will be deleted.
Using output clause i can get the new identity value.. but how can i get the old identity value and insert that into test2 table..
I am not willing to do a self join after the record is inserted into test2. I wanted to get the old ident value during insert of new ident value.
I have a parameter value as shown below and this is dynamic and can grow
Example : 101-NY, 102-CA, 165-GA
116-NY, 258-NJ, 254-PA, 245-DC, 298-AL
How do I get the values in the below format
NY,CA,GA --- each state to be followed with comma and the next state
NY,NJ,PA,DC,AL --- each state to be followed with comma and the next state
correct query that will fetch only state names and not the numbers.
I'm trying to write data to excel from an ssis component to a excel destination.
Even thought I'm writing numerics, every cell gets this error with a green tag:
Convert numbers stored as text to numbers
Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.
I'm hearing this a common problem -
On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:
<style>.text { mso-number-format:@; } </style>
is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.
Maybe some setting in my write drivers - using sql servers excel destination.
So close.. Thanks for any help or information.
I need to extract the .ispac file from the SSISDB. I can retrieve the stream with catalog.get_project sp. However, the file I end up with cannot be unzipped, giving an error message. My guess is that it is meta-data on the zip/ispac file that has a problem, because I can actually unzip it with Winrar, but not with any of those I (programmatically) need to unzip it with.
Below is the code for my stored procedure - My own suspicion is in the BCP usage to turn the stream into a file.
USE [SSISDB]
GO
ALTER PROCEDURE [dbo].[spGetIspacFile]
@project VARCHAR(255) ,
@environmentFolder VARCHAR(50) ,
@ispacTempFolder VARCHAR(100) ,
@ispacFilePath VARCHAR(200) OUTPUT
[code]....
We extract 10k tables every night and I have a table that keeps track of ETL tables that fail or succeed. I would like to know if a table fails during the night and nobody kicks off another job to fix it during the day.
The table structure looks like this:
| Table_Name | Time_Start | Status | Duration | Time_End |
Table_Name = varchar(20)
Time_Start = DateTime
Status varchar(7) = Success or Error
Duration = Number
Time_End = DateTime
Select Table_Name into #MyTempTable
From ETL.STATS_Table
Where Status = 'Error'
AND Cast(Time_Start as Date) = GetDate()
How do I take the table names from #MyTempTable and find out if they where successful for the same date? Duration time and Time_End fields aren't needed.
I have a database table that was designed by someone else, and I'm trying to see if I can normalize the pattern. Here's the dummy table:
CREATE TABLE [dbo].[BadTox](
[PatientID] [int] NULL,
[Cycle] [tinyint] NULL,
[ALOPECIA] [tinyint] NULL,
[Causality1] [tinyint] NULL,
[Code] ....
All the column names in upper case are actually symptom names, and in those columns are values {NULL, 1, 2, 3, 4, 5} and they belong in a column, so the normalized structure should be like this:
CREATE TABLE Symptom (
PatientID INT NOT NULL,
Cycle TINYINT NOT NULL,
SymptomName VARCHAR(20) NOT NULL, -- from the source column *name*
Grade TINYINT NOT NULL -- from the value in the column with the name in uppercase
PRIMARY KEY (PatientID, Cycle, SymptomName));
I can untwist the repeating groups with the code I borrowed from Kenneth Fisher's article [ here ], but the part I'm having a harder time with is grabbing the information that's still left in the column name and integrating it into the solution...
I can retrieve all the column names that are in uppercase using this:
DECLARE @db_id int;
DECLARE @object_id int;
SET @db_id = DB_ID(N'SCRIDB');
SET @object_id = OBJECT_ID(N'SCRIDB.dbo.BadTox');
SELECT name AS column_name
, column_id AS col_order
FROM sys.all_columns
WHERE name = UPPER(name) COLLATE SQL_Latin1_General_CP1_CS_AS
AND object_id = @object_id;
but I can't figure out how to work it into this (that I built by mimicking Kenneth Fisher's article...):
ALTER PROC [dbo].[UnpivotMaxGradeUsingCrossApply]
AS
SELECT PatientID
, Toxicity
, MAX(Grade) AS MaxGrade
[code]....
The problem is that I need to extract the column names (where ToxicityName[n] would be). I can do that by querying the sys.all_columns view, but I can't figure out how to integrate the two pieces. About the only thing I have even dreamed up is to build the VALUES(...) statements dynamically from the values returned by the system view.
So how do I get both the value from the ToxicityName[n] column and the column name into my final data query?
I am using following sql to extract locking information in database. It only work on current selected database, how can I tune to work on all databases and not only currently selected?
SELECT DISTINCT
ES.login_name AS LoginName,
L.request_session_id AS BlockedBy_SPID,
DATEDIFF(second,At.Transaction_begin_time, GETDATE()) AS Duration_Sec,
DB_NAME(L.resource_database_id) AS DatabaseName,
[Code] ....
In my TSQL code i use a derived table to extract the value of account 321 to compare if they are the same that the SUM of my line invoice cost multiply by quantity line : Sum(fi.ecusto*qtt)
This is my script:
SELECT ft.ndoc [Doctype],ft.fno [Docnr] , Sum(fi.ecusto*qtt) [totalcostof my Invoiceline], xctb.conta [accountancy account],
sum(Case when ft.tipodoc = 1 then Xctb.ecre else Xctb.edeb end) as [Value of Cost of invoice in accountancy],
[DIF] = Sum(fi.ecusto*qtt) - Sum(Case when ft.tipodoc = 1 then xctb.ecre else xctb.edeb end)
[Code] ....
My problem is if i have more than on line on my invoice, for example 2 lines, the value of column [Value of Cost of invoice in accountancy] are duplicated, for 3 line invoice the value are multiply by 3.
I have a string and i want to get only the numbers from right.
For example if I have the string Like '123756zxfggr123456' then it will show me only 123456 or if i have the string like
'4vbz67xfggr123dfd' then it will show me only 123 or if i have the string like '123756zxfgg43r5' then it will show me only 5.
I got a function where it gives me all the numbers in a string but I don't need that
CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
[Code] ....
If I ran the select statement it gives me the result 111123456 but i want only 123456 or if i select
SELECT dbo.udf_GetNumeric('111zxfggr6587fhhfkwee') AS 'Num' it will show me 6587.
I would like solving the following issue using the Patindex function i cannot retrieve or extract the single numeric value as an example in the the values below i would like retrieve the Value 2, but in my result set the value 22 also appears or it is completely omitted.
"2 8 7"
"2 8"
"2"
"22"
"3 2 8"
I have tried the following
Patindex('%[2]% [^0-9] [^1] [^3] [^4] [^5] [^6] [^7] [^8] [^9]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col3,
Patindex('[2]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col4,
Patindex('%[2][^0-9]%',Replace(Marketing_Special_Attributes,'^',' ')),
Given a Table1 with two columns 'Name' with some N rows of data and another Table2 with one column 'SeqNo' with N rows, each of which contains a unique integer which can be ordered monotonically, I want to do an INSERT into some Table3 with two columns 'Name' and 'SeqNo' such that each INSERT'd row gets one of the unique integers.
E.g. -
Table1 contains 'Fred','Tom','Mary','Larry'
Table2 contains 6000978,6000979,6000980,6000981
INSERT INTO Table3
SELECT Table1.Name
Table2.SeqNo
FROM Table1
And I want to get Table3
'Fred',6000978
'Tom'.6000979
'Mary',6000980
'Larry',6000981
How can I reference Table2 so that Table2.SeqNo will 'line up' properly? Note that the ordering of the SeqNo values isn't mandatory as long as each SeqNo is assigned to one and only one row.
On edit: Table2 isn't required, it's just the way I started thinking about it. It would be nicer to just have two integer vars, @StartSeqNo = 6000978 and @EndSeqNo = 6000981 for he example above. Either way is fine.
CategoryNos
A 1
A 2
A 3
A 5
A 6
A 9
A 10
B 23
B 24
B 25
B 27
B 28
B 29
B 31
Consider the above data...
I need following output... query ..
CategoryMin RangeMax Range
A 1 3
A 5 6
A 9 10
B 23 25
B 27 29
B 31 31
Logic Behind :
if any number is missing in the continues series the return the min and max of number in that range.
I have a Contact table where I enter a "Parent" (Mother or Father) with IsSubscriber = 1. I also enter all of their children in this same table, with IsDependent = 1.
I then have a Relationship table that relates each child to the appropriate parent record in the Contact table.
I need to assign a sequence number to each child ONLY if they were a multiple birth (twins, triplets, etc.; all have the same DOB). I've been successful at writing a query using ROW_NUMBER(), but it includes the single births (no other child of the same parent has the same DOB).
Stripped down version of Tables and Data and my failed attempt to write a query to do what I want:
IF OBJECT_ID('TempDB..#Contact','U') IS NOT NULL
DROP TABLE #Contact
CREATE TABLE #Contact (
ContactId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
, IsSubscriber BIT
[Code] ....
This is as close as I can seem to get.
I am importing an excel spreadsheet into a MS SQL database table. When the spreadsheet is finished importing, I am noticing that some values that were brought in resemble something like this 1.41666666666667. Other values may be shorter or only have 1 digit. The problem is another web application that pulls this data for use in online forms only allows up to 2 digits. How can I round all of the numbers like the above to 2 decimals and replace the existing values?
I know there is the rounding function that could be used like so:
SELECT ROUND ([Hrs Total 2],2)
FROM AnnualClassifiedPAFs
How do I then take that rounded value and insert it back into the records?
Say you have a table that has records with numbers sort of like lottery winning numbers, say:
TableWinners
num1, num2, num3, num4, num5, num6
33 52 47 23 17 28
... more records with similar structure.
Then you have another table with chosen numbers, same structure as above, TableGuesses.
How could you do the following comparisons between TableGuesses and TableWinners:
1. Compare a single record in TableGuesses to a single record in TableWinners to get a count of the number of numbers that match (kind of a typical lottery type of thing).
2. Compare a single record in TableGuessess to ALL records in TableWinners to see which record in TableWinners is the closest match to the selected record in TableGuesses.
My team is starting to implement error handling in our sprocs. One question we have is whether or not to use unique error numbers for custom errors (ie Errors we throw after doing some sort of validity check, not SQL Server errors). For example, we might check the value of a parameter and then throw an error that says "Parameter State_Date must be less than today, please retry".
We are using SQL Server 2012 and will be using the THROW statement, not RAISERROR, so we don't HAVE to put the numbers in sys.messages. Also, we are going to log the errors in a table, along with the error message, sproc name, line number, etc.
Is it useful to maintain a custom list of error numbers and messages? Or is it just as useful to use one standard error number and add a custom error message (which we can then search for in our code, or use the sproc name & line number we logged)? And if it is worth maintaining a list of numbers plus messages, should we go ahead and put them in sys.messages?
Trying to build a list of order numbers based on stock availability.
The data looks something like this:
OrderNumber Stockcode quantityordered quantityinstock
123 code1 10 5
123 code2 5 10
124 code3 15 20
124 code4 10 10
In this case I would like to output a single result for each order, but based on stock availability order 123 is not a complete order and 124 is so the results will need to reflect this.
I have a column named DATEID in a table DATA that contains the
following format connected to each other(date and name):
20071030Jones
20071020Smith
20070918Rogers
20080122Williams
etc..
What would the syntax be to insert a column to the right of this one,
and extract the first 8 digits from the data in the DATEID column and
insert that into the new column DATE, therefore making it easier for
me to query against an actual date?
Thanks
I have a long text in 'Quote' column as below and i have to extract Trip Duration, Destination and Base Rate from this text. The ‘Base Rate’ will be repeated throughout the text if there is more than one traveler and I only need the first instance.
Begin Quote Calculation<br />
<br />....<br />
Agent Id: 001<br />
Trip Duration: 5days<br />
Relationship Type: Individual<br />....nDestination: AreaTwo<br />
<br ...../>Resolved Trip Type To: 1 with Trip Subtype: 0<br />
Resolved Relationship: Individual....... />
*Base Rates*<br />
Base Rate: 6.070000<br />.....Resolved Trip Type To: 2 with Trip Subtype: 0<br />
Resolved Relationship: Individual....... />
*Base Rates*<br />
Base Rate: 9.070000<br />.....
Result
Trip Duration: 5 days
Destination: AreaTwo
Base Rate: 6.070000
I need to pull certain text from a large varchar field with up to 2 GB-per-instance capacity based on COCustServ
Example Entry:
'KSAUNDERS COCustServ 4/11/2006 5:58:31 PM -- patient called to verify exp date based on letter he received.
SJOY RN 3/27/2006 3:46:56 PM -- Test Ordered: 70460/36yof MANTHONY COCustServ 3/27/2006 4:52:58 PM -- site called to chk sts.'
I will need to pull text in two seperate columns before COCustServ (username) and after COCustServ (date) which could appear multiple times in the same entry. In this case I will need to pull
KSAUNDERS 4/11/2006
MANTHONY 3/27/2006
Can anybody help, thanks
First of all I am a novice here. I am working on a table with a column of URL. I want to seperate the data in the URL delimited by '/'.
Eg:
http://www.simpletech.com/upgrades/aopen/s661fxm/s661fxmintelp4/
Here I want aopen as manufacturer, s661fxm as model_number and intelp4 as submodel_number.
I solved this problem in Oracle using substring and instring. But I have no ides how to achieve this in SQL server.
Please..advice me.
Thanks in advance.
I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
How Extract the data of TFINF when doing text mining? Thanks
View 1 Replies View RelatedHi all,I've the following scenario: One Full Text Search enabled SQL Server Table with one image type field that holds document uploaded from users.The idea is to store binary document(.doc. xls, .pdf, .ppt, html, .xml and so on) and using SQL server full text search i can retrieve record that contains certaind word or words.I have no problems with them. So imagine i do a simple select * from Documents where a=b and i get one collumn with binary document field.With this scenario, i want to "extract" that text from that document + 20 words left and 20 right to show user some info and help him to search desired document (not only for its type or title). Like search engines do.But believe me, i cant find out any component, class or something to do such a thing.I think, the hard work is done with full text search engine...Sql server has that data, but it can decodeI'm so desperate. I would accept answers such "sorry, it can't be done", from a experienced user, but i need to know Thanks in advance
View 1 Replies View RelatedI have a text where it is mix up of all alphanumeric values... For example like this
ACETAMINOPHEN 250 MG ASPIRIN65 MG CAFFEINE
ACER NEGUNDO POLLEN0.0021 G/ML ACER RUBRUM POLLEN0.0021 G/ML ACER SACCHARINUM POLLEN0.0021 G/ML ACER SACCHARUM POLLEN
So my requirement is i need to add comma (,) before every number value in this text.The text is different from each other.But main one what ever the number is there need to add comma before that one (decimal,numeric etc)
ACETAMINOPHEN,250 MG ASPIRIN,65 MG CAFFEINE
ACER NEGUNDO POLLEN,0.0021 G/ML ACER RUBRUM POLLEN,0.0021 G/ML ACER SACCHARINUM POLLEN,0.0021 G/ML ACER SACCHARUM POLLEN
I have run into a problem when searching for a Movie title that is a number (like the movie name 21)
This is an access database and the Movie_Name is a text field
The SQL I am using is
Select * from Movie_Data where Movie_Title = ‘” & Movie_Name & “’”
This works perfectly for everything except when the Movie_Name = a number
Is there a way to make this search work with both characters and numbers?
Does Full text search have problems with numbers?
I have a text field in a table that has been full text indexed. Some of the data in some of the fields start with numbers.
For Example
"123p45 the bla bla bla"
When I run a standard SELECT statement on LIKE '%p4%' it returns the above record.
If I run a full text query using CONTAINS(fieldname,'"*p4*"') it returns nothing.
After much head banging I was able to get the CONTAINS to work when I used *123p4*
I figured since there are alpha characters after the p4 the second * works however since there are no preceeding alpha characters before the p4 it fails.
I would like to be able to do a "full text" search for the "p4" and get the record above.
Any feedback would be appreciated..
I need to extract specific text elements from a varchar column. There are three keywords in any given string: "wfTask," "wfStatus" and "displayReportFromWorkflow." "wfTask" and "wfStatus" can appear multiple times, but always as a pair and will each be followed by by "==" (with or without surrounding spaces). "displayReportFromWorkflow" is always followed by "(" and there can be spaces on either side. The text elements will be between a pair of double quotes, and following one of keywords. For each row, I need to return the task, status and report name.
declare @t table (rowID int, textValue varchar(1024))
insert @t
(rowID, textValue)
values
[Code] ....
Output:
rowID, Task, Status, ReportName
----- --------- ------- ------------------------
1, Issuance, Issued, General Permit
2, Issuance, Issued, Capacity Letter Type III
2, Review, Denied, Capacity Letter Type III
I started with a string splitter using the double quote character, referencing elements "i" and "i+1" where the text like '%wfTask%' or '%wfStatus%' or '%displayReportFromWorkflow%', but the case of multiple task/status in a row has confounded me so far.
Unfortunately, CLR is not an option.