Transact SQL :: Extracting Specific Data From One Field Using Charindex

Jul 20, 2015

I’ve got an issue with extracting specific data from one field using charindex. Here’s an example of the dataset:

Cl_nr Var_data

20059942 ?;;300134BL10;?;;;;;;
20059958 ;2698;020225PU20;?;;;;;;
20059975    ;;100777ST20;?;;;;;;
20059976 ;;;;;;;;;
11001980 ;;051168PU20;?;;;;;;1001980
20034832 ;;060253BO10;?;;;;;;
20055246 ;;1108731;?;?;;;;;
20043656   ;;1022509;?;;;;;;
20059366 ;;1181750;31-12-2015;2;;;;;
20052712    ;;230626NO10;?;;;;;;

Goal is to get the data after the 2<sup>nd</sup> ; until the next ; starts. Ideal would be to catch everything between the 2<sup>nd</sup> ; en 3<sup>rd</sup> ; (number should be 10 characters).

If I try to select this data just using charindex it only goes until it finds the first ; (of course), what’s the best approach in this?

View 4 Replies


ADVERTISEMENT

String Index Function (substring / Charindex) - Extract Specific Characters From Data

Aug 5, 2013

SQL Query. What i need is to be able to extract specific characters from data.

Eg name

1.2-KPIA1-App-00001 this is a name i require, but i also require the '1.2' and the 'KPIA1' to be displayed in new columns in the results

i.e. 1.2-KPIA1-App-00001 1.2 KPIA1

*I need this in part of a script as there is thousands of rows of data.

View 4 Replies View Related

Transact SQL :: Converting A Value Using Charindex

Jul 10, 2015

What is the best way to convert 3/1/1/12/0 into xxx-xx-xx-xx-xx format by appending zeros if required. final result should be
300/10/10/12/00.

View 9 Replies View Related

Extracting Specific Values From One Table And Insert Them Into Another

Jun 3, 2008

Hello. I have a somwhat simple question.
I have a table inside my database where i have some columns. Id like to extract distinct values from one column and inser them into another. My table is named article and id like to slect all the destinct values from the column naned type inside my article table. Then i need to insert my values into a new table called type but i would also like the to have 2 columns inside my type table. 1 called counter witch is an auto increment PK, and another one named type where the results from my query would be inserted.
Iv tried a veriety of querys but none of them have managed to do this.
Could anyone help me construct this query?

View 2 Replies View Related

Transact SQL :: INSERT Specific Data To Other Table

Aug 10, 2015

I am still learning T-SQL .Lets consider the table below, ID 1-3 shows our purchase transactions from various Vendors and ID 4-6 shows our payments to them

Table 1 - VendorTransactions

ID     PARTY    AMOUNT   VOUCHER
---------------------------------------
1       A               5000              Purchase
2       B               3000              Purchase
3       C               2000              Purchase

4       A               3000              Payment
5       B               1000              Payment
6       C               2000              Payment
7       A               1000              Payment


Now we have a blank table Table 2 - Liabilities

ID     PARTY    AMOUNT 

I want that SQL should look for each individual party from Table 1 and Calculate TOTAL PURCHASE and TOTAL PAYMENTS and then deduct TOTAL PAYMENTS from TOTAL PURCHASE so we get the remaining balance due for each party and then add the DIFFERENCE AMOUNT alongwith PARTY to the TABLE 2 so I can get the desired result like below

ID     PARTY    AMOUNT
-------------------------
1       A               1000              
2       B               2000              
3       C               0       

View 3 Replies View Related

Transact SQL :: Can Add 1 To Charindex Function But Can't Minus 1

Aug 25, 2015

The charindex can run this

LEFT([Description], CHARINDEX('(', [Description]) + 1)  as NewDesc,

But I can't run

LEFT([Description], CHARINDEX('(', [Description]) - 1)  as NewDesc,

View 5 Replies View Related

Transact SQL :: Flattening Hierarchical Data In Specific Format

Apr 22, 2015

I have the data in this format

ID        NAME         ParentID
CV1      CV1NAME      CV
CVX1    CVX1NAME    CV1
CVXX1  CVXX1NAME  CVX1
CV2      CV2NAME     CV
CVX2    CVX2NAME    CV2
CVXX2  CVXX2NAME  CVX2

How can i flatten this data into this format

CVID    CVNAME     CVXID   CVXNAME   CVXXID   CVXXNAME
cv1        cv1name  cvx1        cvx1name    cvxx1     cvxx1name
cv2        cv2name  cvx2        cvx2name    cvxx2     cvxx2name

View 4 Replies View Related

Transact SQL :: How To Select Data With Specific Priority Of A Column

Aug 1, 2015

i have table that has rows as below

DECLARE @Table TABLE
(minv_code INT,
alert_msg varchar(10),
alert_time Datetime)

[code]...

i want to select the data priority wise. the o/p should look like 

e.g first row - 873939, 'Meter', '7/24/2015 3:31:22'
second row - 873939, 'Tamper', '7/24/2015 3:30:00'
third row - 873939, 'Reverse', '7/24/2015 3:31:18'
fourth row -873940, 'Tamper', '7/24/2015 3:31:22' 
fifth row - 873940, 'Reverse', '7/24/2015 3:30:00'

View 6 Replies View Related

Transact SQL :: Select Specific Values From All Rows Where Value Of A Specific Column Is (Active)

May 23, 2015

I need to select specific values from all rows where the value of a specific column is "Active"

This part works: SELECT LastName, FirstName, MiddleInit, ClientId FROM dbo.Client

But I want to add: WHERE StatusType = (Active) and how to do this.

View 4 Replies View Related

Extracting Substring Into New Field

Dec 20, 2007

I am trying to populate two fields in my table with the first 6 characters in an existing field with the following command:

SELECT dimension_elist,
SUBSTRING(dimension_elist FROM 1 FOR 6)
AS BU_Number
SUBSTRING(dimension_jde_pl FROM 1 FOR 6)
AS Acct_Number
FROM JDE_Import

The result is an error:
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'FROM'.

I am fairly new to SQL so I am probably missing something basic.

Does anyone have any thoughts?

View 2 Replies View Related

Transact SQL :: How To Get Value Of A Particular Field From XML Data

Nov 6, 2015

We are running the following querry which insert data row from an XML source

INSERT INTO End_ReelProcessValue
(LineId
,ReelHist_id
,Variable_tagName
,ProcessMeasure
)

[Code] ....

From that querry, I need to store in a variable the Value of a particular XML entry in order to store it in an other table.The XML parsing is based on the following lines :

[Variable_Tagname] CHAR(50) '@Name',
[ProcessMeasure] NVARCHAR(254) '@Value'

What I need is that if the [Variable Tagname] issue from entry @Name is equal to the string "Quality", then I need to store its value to a variable.Then an other querry will use that variable to insert it to an other table.

How can I retrieve that particular record ?

View 2 Replies View Related

Transact SQL :: Extracting A Word From String?

Jun 30, 2015

Is there a function / way to extract a word from a particular position in the String.

I want to extract a word which is in the 16th position. All words are separated by spaces.

View 6 Replies View Related

Update Field With Trigger Only If A Specific Field Is Updated

Nov 11, 2013

I want to update a field with a trigger only if a specific field is updated.

When I try the code below, it updates the field when any field in the record is updated. Is there a way to only make look at picked_dt?

ALTER TRIGGER [dbo].[UpdatePickedDate]
on [dbo].[oeordlin_sql]
after update
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from

[Code] .....

View 4 Replies View Related

Help On Extracting Only With Three Capital Letters From The Field

Feb 12, 2008

Hi Everybody

I have this situation where I need to extract values from a field that satisfies this criteria...

ONLY THREE LETTERS AND
ALL IN CAPITAL LETTERS

thanks

View 14 Replies View Related

Extracting Time From The SQL DateTime Field.

Sep 8, 2005

Any help on extracting the time part from the datetime field in SQLdatabase.even though i had entered only the time part in the database when iextract the field it gives me only the date part. i’m using Vb.netdatagrid as a front end.any assistance appreciated!! :?:--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict254266.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=877989

View 1 Replies View Related

Transact SQL :: Extracting Certain Text After Special Character

May 18, 2015

I would like to write a SQL to extract a text after certain _ special character.Example : Relationship_Person_Type_Aunt

View 9 Replies View Related

Transact SQL :: Extracting From 3 Related Tables And Including MAX Function

Oct 5, 2015

I have these 3 tables in a database for printed chamber music works:

Table INSTRUMENTS: List of musical instruments
    Column ID          -- int, primary key
    Column sInstrument -- string

Table PTRS_INSTRUMENTATION: 
    Column ID          -- int, primary key
    Column lInstrument -- int, points to a row in table INSTRUMENTS via ID
    Column lMainItem   -- int, points to a row in table MAINITEMS via ID
    Column lInstrSeqNum -- int, sequential number of like instruments within one Main Item
    
Table MAINITEMS: List of chamber music works
    Column ID          -- int, primary key
    Column sItem       -- string
    
Each chamber music work (Main Item) requires an ensemble of musicians playing several instruments. Some of them may be the same, such as 2 violins. A classical quartet, for instance, consist of 2 violins, 1 viola and 1 cello. I am concerned here only with one of the instruments (although it may occur several times).

I want to extract a list of Main Items where the instrument is given (e.g., 'violin'),

And the maximum of PTRS_INSTRUMENTATION.lInstrSeqNum is a given number (e.g., 2).

View 7 Replies View Related

SQL Server 2008 :: Replicate Only Specific Data To Specific Subscriber?

Jun 30, 2015

We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.

Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?

View 2 Replies View Related

CHARINDEX With INT Data Type (Multiple Value)

Mar 24, 2014

I have a column that has 50 records and they are INT. I am trying to place them into a single parameter using the CHARINDEX using the IF Statement. I got an error saying convertinh from varchar to int type data.

Basically, this is what I did:
@RoomID varchar(max)

Then I declare it:

DECLARE @RoomId varchar(4)

SET @RoomID = 0

The IF part that I need to express this CHARINDEX function.

Then in Where clause I filtered it as:
WHERE r.roomid = @RoomId

Or what other way to handle INT data type and make them available for user to choose multiple value?

View 1 Replies View Related

Transact SQL :: BULK INSERT Not Importing Correct Data For Field?

May 14, 2015

I am using a BCP format file to import a CSV file. The file looks like the following:

"01","02"

The format file looks like the following:

6.0                                                                                     
2                                                                                      
1      SQLCHAR    0      0       """         0    ""
2      SQLINT       0      0       "",""     1   MROS
3      SQLINT       0      0       ""
"   2   MROF

When both the two fields are set to SQLCHAR data types the data imports successfully without the quotes as 01 and 02.  These fields will always be numbers and I want them as integers so I set the data type to int in the database and SQLINT in the format file.  The results was that the 01 became 12592 and the 02 became 12848.  where these numbers are coming from?

View 7 Replies View Related

Assign Specific Data To Specific Users

Mar 21, 2008

I am very early on in developing a website to track issues with projects which is tied to a SQL database.  I have my Projects Table, my Users Table, and am creating a third table to track issues.  I'm wondering what is the best way to assign specific users to specific data/projects.  The user should only be able to view & update the projects assigned to him.  He should not be able to see other projects.  What is the best way to assign projects/data to the users to make sure they are only viewing their data?

View 1 Replies View Related

Alternative For LEFT() And CHARINDEX() That Works With Text Data Type

Jun 26, 2007

I have a stored procedure that receives a list of ids, to get the emails of each of those ids. The problem that I'm having is that I'm using a char data type that is max length is 8000, but the contact lists are getting bigger and soon they might reach the variable max length. The contact list that I receive will look something like this "1234,67523,67875,789687,", I'm using "," as a separator. Right now what I do is this
@array_value = LEFT(@ContactList, @separator_position - 1)
 
The LEFT function doesn't work with data types text and ntext. But I'm in need of a string data type with a max length bigger than 8000. So I will apreciate if anyone knows of another function that does the same or similar and works with text data type, or any other ideas will be welcome.
 
Thanks,
 Erick

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

Transact SQL :: Return Field When A Field Contains Text From Another Field

Aug 25, 2015

I'm new to SQL and I'm trying to write a statement to satisfy the following:

If [Field1] contains text from [Field2] then return [Field3] as [Field4].

I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.

View 8 Replies View Related

TSQL - Copy Table As New Table And Get The Sum Of Specific Field Group By Other Field

Sep 4, 2007

Hi guys,
I need to get a column with the sum of the field "SUF" from table "JurnalTransMoves_1" when that field ("SUF") is ordered by the field "REFERENCE" from table "Stock", and Show the value only once.

The desired result should by something like:








Stock.REFERENCE
JurnalTransMoves.SUF
SUM(JurnalTransMoves.SUF) Group By Stock.REFERENCE

5752
10
60

5752
20


5752
30


5753
400
3000

5753
500


5753
600


5753
700


5753
800


5754
7
15

5754
8



Is there any chance to do that?
Thanks in advance,
Aldo.




Code Snippet
SELECT
Accounts.FULLNAME AS 'ACCOUNTS.FULLNAME',
Accounts.ACCOUNTKEY AS 'ACCOUNTS.ACCOUNTKEY',
Accounts.FILTER AS 'ACCOUNTS.FILTER',
Accounts.SORTGROUP AS 'ACCOUNTS.SORTGROUP',
AccSortNames.SORTCODENAME AS 'AccSortNames.SORTCODENAME',
Accounts.CreditTermsCode AS 'Accounts.CreditTermsCode',
CreditTerms.DETAILS AS 'CreditTerms.DETAILS'
CreditTerms.CURRENF AS 'CreditTerms.CURRENF'
CreditTerms.MONTH AS 'CreditTerms.MONTH',
CreditTerms.DAYS AS 'CreditTerms.DAYS',
CreditTerms.SHAREPRC AS 'CreditTerms.SHAREPRC',
CreditTerms.TEMF AS 'CreditTerms.TEMF',

CASE
WHEN CAST(Accounts.VatExampt AS int) = 0 THEN 'x'
WHEN CAST(Accounts.VatExampt AS int) = 1 THEN 'y'
ELSE 'Undefined' END AS 'VAT',

Stock.DOCUMENTID AS 'Stock.DOCUMENTID',
DocumentsDef.DOCNAME As 'DocumentsDef.DOCNAME',

CASE
WHEN CAST(Stock.DOCUMENTID as int) = 1 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 3 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 35 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 120 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 31 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 44 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 34 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 43 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 40 THEN Stock.REFERENCE
ELSE '' END AS 'Invoice No',

Stock.VALUEDATE AS 'Stock.VALUEDATE',
JurnalTrans.DESCRIPTION AS 'JurnalTrans.DESCRIPTION',
JurnalTrans.REF2 AS 'JurnalTrans.REF2',
JurnalTransMoves.SUF AS 'JurnalTransMoves.SUF',
JurnalTransMoves_1.SUF AS 'JurnalTransMoves_1.SUF',
JurnalTransMoves.TRANSID AS 'JURNALTRANSMOVES.TRANSID'

FROM
JURNALTRANSMOVES AS JurnalTransMoves_1
INNER JOIN JURNALTRANSMOVES AS JurnalTransMoves
INNER JOIN (SELECT DISTINCT JURNALTRANSID, RECEIPTSTOCKID, FULLMATCH, TABLFNUM, CKCODE, RSORT, RUSEFID FROM RECEIPTJURNALMATCH) AS ReceiptJurnalMatch_1 ON ReceiptJurnalMatch_1.JURNALTRANSID = JurnalTransMoves.ID
INNER JOIN ACCOUNTS AS Accounts ON JurnalTransMoves.ACCOUNTKEY = Accounts.ACCOUNTKEY
INNER JOIN JURNALTRANS AS JurnalTrans ON JurnalTransMoves.TRANSID = JurnalTrans.TRANSID
INNER JOIN STOCK AS Stock ON JurnalTrans.STOCKID = Stock.ID ON JurnalTransMoves_1.TRANSID = JurnalTrans.TRANSID AND JurnalTransMoves_1.ACCOUNTKEY = Accounts.ACCOUNTKEY
LEFT OUTER JOIN ITEMS AS Items
INNER JOIN STOCKMOVES ON Items.ITEMKEY = STOCKMOVES.ITEMKEY
INNER JOIN ITEMSORTNAMES AS ItemSortNames ON Items.SORTGROUP = ItemSortNames.ITEMSORTCODE ON Stock.ID = STOCKMOVES.STOCKID
LEFT OUTER JOIN ACCSORTNAMES AS AccSortNames ON Accounts.SORTGROUP = AccSortNames.ACCSORTCODE
LEFT OUTER JOIN CREDITTERMS AS CreditTerms ON Accounts.CREDITTERMSCODE = CreditTerms.CREDITTERMSCODE
LEFT OUTER JOIN DOCUMENTSDEF AS DocumentsDef ON Stock.DOCUMENTID = DocumentsDef.DOCUMENTID

WHERE
Accounts.SORTGROUP Between '3001' And '3020'
AND Accounts.ACCOUNTKEY IN ('123456')

ORDER BY Accounts.ACCOUNTKEY

View 22 Replies View Related

A Default Value For A Specific Field/s

Mar 25, 2008

hi, please help me, i have a data driven  site, i made a field that i call "Status"so this is how it should happens, there are three different gridviews in a single page, gridview1 filter is [status] = ok, gridview2 filter is [status] = fine, gridview3 filter is [status] = complete, so all gridview has its own  <asp:hyperLinkField> at first the value of "Status" is ok, so it will appear in the gridview1, so when the user click on the <asp:HyperLinkField> of gridview1 it will go to a new page with a form view, this page has a button that when click will change the value of the status field to "fine", so that it will no longer appear to the gridview1 but it will now appear to gridview2 and same with the next <asp:HyperLinkField> click until it appears to the final gridview. so my question is how can i make the button that is capable of changing (update) the value my database field (status) to a specific value, that is not visible to the user thru codebehind or plain asp.net codes. please help me, if you need more info just reply.ThanksSALAMAT PO! 

View 1 Replies View Related

Select With Or In A Specific Field

May 25, 2008

I've a select SELECT Field1, Field2, Field3 FROM Tablethe problem is that I can have filled Field1 OR Field2 ... I would like to create a new column in the result for see only the filled field  if Field1 is = null or  = ' ' I want to see Field2 if Field2 is = null or  = ' ' I want to see Field1

View 2 Replies View Related

Get A Count On A Field For A Specific Value

Sep 13, 2013

I am attempting to combine to queries that I created and am not having success. I need to get a count on a field for a specific value and have to do this by gender and by an age range. So here is the query I came up with. I don't get any errors produced but I also don't get any results.

$query = "SELECT p1.sex, age_group, COUNT(CASE WHEN p4.q1 = 'Yes' THEN p4.q1 END) AS heart_attack FROM (select p1.sex,".
" CASE WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) BETWEEN 11 AND 20 THEN '11-20'".
" WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate()))

[code]....

View 8 Replies View Related

Select Max If Value Of Specific Field Is Y

May 15, 2014

I need to check if the latest record in a table for the current client, based on EFFECTIVE_DATE, has a value of 'Y' in the STATUS field. Sample records for client #19:

CLIENTEFFECTIVE_DATESTATUS
------ -------------- ------
19 08/13/2010Y
1901/10/2013N
1902/03/2014Y

If I run:
SELECT CLIENT, max(EFFECTIVE_DATE), STATUS
FROM FTABLE GROUP BY CLIENT, STATUS

I get the two latest records since STATUS is different:

CLIENTEFFECTIVE_DATESTATUS
------ -------------- ------
1901/10/2013N
1902/03/2014Y

I wrote the following and it works, but it may be causing 'Insufficient Disk Space' error from the database. Is there any way to write this query without temp table?

DECLARE @TEMPMAX

TABLE (CLIENT INT, EDATE DATE)
INSERT INTO @TEMPMAX (CLIENT, EDATE)
SELECT CLIENT_NO, max(EFFECTIVE_DATE)

[Code] ....

CLIENTEFFECTIVE_DATESTATUS
------ -------------- ------
1902/03/2014Y

View 3 Replies View Related

Transact SQL :: NVARCHAR Field Getting Truncated After Increasing Field Length?

Aug 28, 2015

I have a very strange situation. I've increased the size of an NVARCHAR field from 8 to 9 in a database table. The format of the data that I enter will either be an 8 character field (123456-8) or a 9 character field (1234567-9). The '-' is critical.

It used to only accept the 8 character version, but after increasing the field size, if I try to insert the 9 character field version, it gets truncated after the '-', as though it's still only allowing 8 characters. But that only occurs when I include the '-' or other such characters like '#'. If I try to insert 1234567a9, it works. The following explains the outcomes:

Inserted Value -> Result in table

123456-8 -> 123456-8      *Correct
1234567-9 -> 1234567-     *Wrong
123456789 -> 123456789    *Correct
1234567#9 -> 1234567#     *Wrong
1234567a9 -> 1234567a9    *Correct

Why is it that characters such as '-' and '#' are truncating the value, but only if the string is 9 chars long?

I'm currently using a direct t-sql insert statement in SQLExpress. The field is a simple NVARCHAR(9) field.

View 3 Replies View Related

Transact SQL :: DDL Trigger For Specific Tables

Oct 14, 2015

How to set ddl triggers for specific tables, instead of on entire database.

View 2 Replies View Related

Transact SQL :: Select With Value For Specific Month

Nov 13, 2015

I have table with below scheme

Year      Month      Day      RELH     
------    --------       ------     -------
1973       4            1            50
...... etc
1973      4             30           20 
.....etc
1974       5             1              50
....etc
1974        5            30             99

I need to get years where average of RELH  of  months within year meet 

Month 4 Average(RELH) > 33 and
Month 5 Average(RELH) > 60 and 
Month 6 Average(RELH) < 33 

How can I achieve this?

View 4 Replies View Related

Transact SQL :: Capture Any Call For Specific SP

May 4, 2015

Is there a way to capture any firing for a SP such as (datetime , loginname,..) and not using SQL profiler ?

View 3 Replies View Related







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