SQL Server 2012 :: Getting String Out Of Data From A Column?

May 7, 2015

I have system id information in table system_ids and productids and systemidinsformation has lot of data but I am looking two strings in tire data to pull into two separate columns. details below

Database versions :ms sql 2008/2012
tablename:system_id's
column:system id information

sample data from system_id_information column

########################################
<obj xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:vim25" versionId="5.5" xsi:type="ArrayOfHostSystemIdentificationInfo"><HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo"><identifierValue> unknown</identifierValue><identifierType><label>Asset Tag</label><summary>Asset tag of the system</summary><key>AssetTag</key></identifierType>

[Code] .....

I am looking output of two columns, which are bolded

product_id snumber
654081-B21 MXQ43905SW

for serial number this is common

before string :HostSystemIdentificationInfo"><identifierValue>

and after string </identifierValue><identifierType><label>Service tag

and snumber is always between the before and after string and number of characters of snumber varies and entire data for a row also varies

View 9 Replies


ADVERTISEMENT

SQL Server 2012 :: String Format To Be Formed Out Of A Column

Nov 3, 2015

I am trying to find a solution to get the result set to fetch a particular string format from a table in my database, which has a column of NVARCHAR data type

CREATE TABLE #ActivityComments(Comments NVARCHAR(MAX))

INSERT INTO #ActivityComments VALUES('This is the study code for Field Phase S14-04932-01')
INSERT INTO #ActivityComments VALUES('Phase reporting has the study S15-04931-01 which is obselete')
INSERT INTO #ActivityComments VALUES('Phase running study code S14-04932-02 is not valid')

The output of the query should be like:

S14-04932-01
S15-04931-01
S14-04932-02

Is there any way possible to achieve this..

View 7 Replies View Related

SQL Server 2012 :: Remove String From Column Post Code Field

Sep 8, 2015

I am querying with a SELECT statement against an address table that has a post code column with corrupt data.

Sample record:- Northants NN4 0NB
Should be NN4 0NB

I have used the following on another column:-

LEFT(A.Addr1,CASE WHEN CHARINDEX ('(', A.Addr1) =0 THEN LEN(A.Addr1) ELSE CHARINDEX('(' ,A.Addr1) -1 END) AS 'Address 1'

but unable to correct this problem?

View 9 Replies View Related

SQL Server 2012 :: Query Get Data In Double Codes In String

Feb 9, 2014

I have data like this

"entitlementwrapper" : [ {
"Type" : "Factory Warranty",
"Date_Type" : "Ship date",
"Status" : "Active",
"Start_Date" : "2012-12-21",
"End_Date" : "2014-01-19",
"Days_Left" : "116",
"Term" : "13",
"Description" : "Wty: HP HW Replacement Support",
"IsTrusted" : "Y",
"Transaction_ID" : "4644780453"
}

I want to get only data in double codes in using sql query.

View 9 Replies View Related

SQL Server 2012 :: Convert Column Data Into Row Data?

Dec 12, 2014

I have data like this in a table

ID test_date Score
001 4/1/2014 80
001 5/4/2014 85
001 6/1/2014 82

I want to convert the data into a row like this:

ID test_date1 score1 test_date2 score2 test_date3 Score3
001 4/1/2014 80 5/4/2014 85 6/1/2014 82

How can I do that with T-SQL?

View 1 Replies View Related

SQL Server 2012 :: How To Get A Sum Of One Set Of Data In Same Column

Sep 6, 2015

I put on the site excell file where I explained what I want to get . It is easier to explain through an example .

THIS IS MY INPUT DATA:

ACCOUNTS CODE ACCOUNTS NAME TOTAL

0 CLASS 0
011 Synthetic group
011-1301 Some name 1 25 $
011-1401 Some name 2 20 $
022 Synthetic group
022-1011 Some name 1 101 $

[code]...

View 9 Replies View Related

SQL Server 2012 :: Display Data In Blocks (Column Name)

Jan 29, 2014

I want to display data in block. Below is the Create,Insert Script and format of desired output.

CREATE TABLE [dbo].[Test_AA](
[CounterpartID] [varchar](2000) NULL,
[CounterpartName] [varchar](2000) NULL,
[SATName] [varchar](2000) NULL,
[NameOfBO] [varchar](2000) NULL,

[Code] ....

Result Should be like This :

ID:17388
Name:Scottie Berwick
SATName:Scottie Berwick
NameOfBO:
SATAccountNumber:

[Code] ....

View 4 Replies View Related

SQL Server 2012 :: How To Extract One More Piece Of Data From The Column

Dec 17, 2014

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?

View 5 Replies View Related

SQL Server 2012 :: Find Out If Whole Column Of Data In A Table Is Empty?

Dec 2, 2013

I have created some dynamic sql to check a temporary table that is created on the fly for any columns that do contain data. If they do the column name is added to a dynamic sql, if not they are excluded. This looks like:

If (select sum(Case when [Sat] is null then 0 else 1 end) from #TABLE) >= 1 begin set @OIL_BULK = @OIL_BULK + '[Sat]' +',' END

However, I am currently running this on over 230 columns and large tables 1.3 mil rows and it is quite slow. How I can dynamically create a sql script that only selects the columns in the table where there is data in a speedier manner. Unfortunately it has to be on the fly because the temporary table is created on the fly.

View 9 Replies View Related

SQL Server 2012 :: Store Tabular Data Having Parenthesis As Column Name Into XML

Nov 9, 2014

We are storing changed data of tables into XML format for auditing purpose. The functionality is already achieved. We are using FOR XML Path clause to convert relational data of tables into XML format.

Now, a table is having column name with '(' . For example name of the column is, ColumnName(). In this case we can not convert into XML using For XML clause. Showing error as,

Column name 'columnName()' contains an invalid XML identifier as required by FOR XML; '(' (0x0028) is the first character at fault.

View 1 Replies View Related

SQL Server 2012 :: New Column In Existing Table And Uploading Data

Jan 13, 2015

Need to change the datatype of existing column which has huge data.

I'm performing below steps

1. Create new column with correct datatype in the same table
2. copy data into new column
3. drop indexes on column
4. <<<>>>
now the existing column also has many SP dependent and I do not wish to drop them.
5. rename existing column to xxx
6. rename new column to correct column
7. drop old column
8. make required indexes

View 9 Replies View Related

SQL Server 2012 :: Splitting Column Value While Keeping Existing Data?

Jun 22, 2015

Currently I have a column with multiple postcodes in one value which are split with the “/” character along with the corresponding location data. What I need to do is split these postcode values into separate rows while keeping their corresponding location data.

For example:

PostCodeLatitudeLongitude
66000/6610042.6965952.899370
20251/2027042.1964719.404951

Would become

PostCodeLatitudeLongitude
6600042.6965952.899370
6610042.6965952.899370
2025142.1964719.404951
2027042.1964719.404951

how this can be done?

View 2 Replies View Related

SQL Server 2012 :: Table With Column Data - Convert Varchar To Int

Jul 8, 2015

I have a table with column "Data" as VARCHAR, with entries like below.

1
11
2
A1
A10
A11
246
AB1
AB10
100
256
B1
B2
124
20
B21
B31
32
68

I want to select the data by converting varchar to int for numeric values and for alphanumeric it should display as it is.

SELECT CAST(dataAS INT) FROM record_tab

getting below error

Conversion failed when converting the varchar value 'A1'

View 9 Replies View Related

SQL Server 2012 :: Finding Longest String Within A String Field

Mar 20, 2014

We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.

View 9 Replies View Related

SQL Server 2012 :: Determine Which Column Is Causing Error Converting Data Type Varchar To Numeric?

Aug 14, 2014

I'm moving data from one database to another (INSERT INTO ... SELECT ... FROM ....) and am encountering this error:

Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.

My problem is that Line 6 is:

set @brn_pk = '0D4BDE66347C440F'

so that is obviously not the problem and my query has almost 200 columns. I can go through one by one and compare what column is int in my destination table and what is varchar in my source tables, but that could take quite a while. How I can work out what column is causing the problem?

View 3 Replies View Related

Transact SQL :: Key And Indexes On Two Column Data Table Or Parsed View (Large String Of Data And Filename)

Oct 4, 2015

I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.

Example column A:
(name phone house cost of house,zipcodecountystatecountry)
-a view will later split this large varchar string based 
column b: is the source filename of the data load (varchar 256)
....

a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)

b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?

c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.

-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?

View 4 Replies View Related

SQL 2012 :: String Splitting Using Like And Data Cleansing

Mar 28, 2014

I have a large poorly designed table (inherited) With a Name field that contains comma delimited text containing address information. I need to do several things with it but unfortunately there doesn't appear to be any true consistency in it. When it displays in its own text box it works by placing each section on a new Line and looks ok.But I need to pull it apart and place things like unit number, Building Name in its own column etc. In the data it could be in either the 2nd,3rd, 4th, dependent on what came 1st. the data looks some thing like the following

unitNumber/StreetNumber Space StreetName (Building Name), Subub,City,Country

Some addresses won't have unit number or Suburb or country so when splitting you could have Suburbs and Citys in multiple columns even if you try and stagger the split process.Has any body go a good tool or reference site for dealing for this sort of problem. I have a table that I have made up that has some of the street names that could be used for comparing against existing records but it is by no means fool proof due to spelling inconsistencies . I also have another list of Common building names that could be used to compare, remove and place in the new building column.

View 1 Replies View Related

SQL 2012 :: Parsing Out Data String With Text Delimiters

Nov 5, 2014

I'm trying to parse out a line of data that is separated by the text "atc1.", "atc2." etc.

For example,

[atc1.123/atc2.456/atc3.789/atc4.xyz/]

If I only want the data after atc2., then I could search the string for "atc2." and collect all the characters afterwards. But how can I make sure to trim off all the data after "atc3." to make sure I'm only collecting "456" from the example above?

View 2 Replies View Related

Need To Combine String Data From Multiple Columns Into One Column

Aug 31, 2007

When quering a table with given criteria, For ex:

select notes, jobid, caller from contact where status in (6) and jobid = 173
I am getting this:




This job will be posted to Monster for 2 weeks. 173 906
Waiting for full budget approval 173 906
TUrns out we're uppin 173 906

What should I do so that these three columns for the same jobid from the same caller appears in only one column, either separated by a comma or semicolon?

Please HELP!!!!!

View 4 Replies View Related

SQL Server 2012 :: Subtract / Exclude Value Items From A Column And Add It To Another Column In Same Table

May 26, 2014

I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.

View 1 Replies View Related

SQL Server 2012 :: Format Value Of A Column And Insert It Into Another Column Of The Same Table?

Sep 18, 2014

A column of a table has values in the format - 35106;#Grandbouche-Cropp, Amy.

I need to format the column data in such a way that only the text after # (Grandbouche-Cropp, Amy) remain in the column.

The text before ;# (35106) should be inserted in to another column of the same table.

Below is the table structure:

create table [HR_DEV_DM].[CFQ_TEST].sp_CFQ_Commercial_Referrals
(
ID int identity,
PromotionalCode nvarchar(4000),
QuoteNumber nvarchar(100),
CreatedBy nvarchar(100),
Created datetime,
ModifiedBy nvarchar(100),
Modified datetime,
CreatedBy_SalesRepSharePointID int,
ModifiedBy_ModBySharePointID int
)

View 2 Replies View Related

Updating Column With Info - String Or Binary Data Would Be Truncated

Jan 29, 2015

I wrote this query to pull some information involving 3 tables dbo.NewFamNbrs$, dbo.CGIItemMaster and dbo.CGIFamilyMaster. In addition I want to perform an update statement to update column dbo.CGIItemMaster.FamilyIDX with the information of dbo.CGIFamilyMaster.IDX. I also know that truncation is indicating that I'm trying to insert data into a field not large enough. So I performed a len() function on the both columns and order by desc and they both came up with 4 as being the max.

Query:

SELECT a.PART, a.Family, b.FamilyIDX, c.FamilyID, c.IDX
FROM dbo.NewFamNbrs$ a LEFT OUTER JOIN dbo.CGIFamilyMaster c ON a.Family = c.FamilyID LEFT OUTER JOIN dbo.CGIItemMaster b ON a.PART = b.PART

Results of query:

PART Family FamilyIDX FamilyID IDX
000127233TF011468 TF01 506
000129880TF011468 TF01 506
003110 MET061468 MET06 1915

UPDATE STATEMENT:

begin tran
update dbo.CGIItemMaster
set FamilyIDX=c.IDX
FROM dbo.NewFamNbrs$ a LEFT OUTER JOIN
CGIFamilyMaster c ON a.Family = c.FamilyID LEFT OUTER JOIN
dbo.CGIItemMaster b ON a.PART = b.PART

View 2 Replies View Related

Transact SQL :: Find Table And Column Names From String Data

May 29, 2015

I have a SQL text column from SP_who2 in table #SqlStatement:

like 1row shown  below :

 "update Panel  set PanelValue=7286 where PanelFirmwareID=4 and PanelSettingID=9004000"

I want to find what table and column  names are in the text ..

I tried like below ..  

Select B.Statement from #sp_who2 A  
LEFT JOIN #SqlStatement B ON A.spid = B.spid 
 where B.Statement IN (
SELECT T.name, C.name FROM sys.tables T
JOIN sys.columns C 
ON T.object_id=C.object_id
WHERE T.type='U'


Something like this : find the column names and tables name

View 18 Replies View Related

String Or Binary Data Would Be Truncated When Using Default Column Definition

Nov 22, 2007



When I set a column to have a default definition that uses a UDF, I am receiving the "String or binary data would be truncated" error.

The UDF:




Code BlockALTER FUNCTION GetDefaultClientTier
(
@ClientAssets decimal(15,2)
)
RETURNS char(1)
AS
BEGIN
DECLARE @Result char(1)
-- Get the first result in case of overlaps.
SET @Result = CAST((SELECT TOP 1 ClientTier FROM ClientTiers WHERE @ClientAssets BETWEEN ClientAssetsFloor AND ClientAssetsCeiling) AS char(1))
RETURN @Result
END





ClientTier is defined as char(1) in the table. I simply have (isnull([dbo].[GetDefaultClientTier]([ClientAssets])),(null))) as the definition. I can't use a computed column because I the values need to be editable. When inserting with SSIS, the insert works fine but the column has a value of null for each row.

When putting a character as the default (like 'A') the insert works fine.

The cast is there only because I have tried everything I can think of to get around this.

Is there something simple I am overlooking?

(SQL2005 SP2)

Thanks!

View 3 Replies View Related

SQL Server 2012 :: Group Column Based On Another Column

Jul 11, 2014

I have Table Like this

t_id w_id t_codew_name
358553680A1100EVM Method Project
358563680A1110EVM Method Project
358453684A1000Basic
358463684A1010Basic
358473685A1020Detail

[Code] ....

View 1 Replies View Related

SQL 2012 :: Turn Row Data To Column

Aug 19, 2015

ItemID ItemName Price
1 Test 1 100
1 Test 1 200
2 Test 2 130
2 Test 2 150
3 Test 3 300
3 Test 3 400
...

How to write a query to bring price data to column so the Item is not duplicate any more.

The result should be:

Item ID ITemName PRice1 PRice2
1 Test1 100 200
2 Test2 130 150
3 Test3 300 400

View 9 Replies View Related

SQL Server 2012 :: SELECT A String

Dec 1, 2014

I have string as below:

InvoiceTemplateId= SOURCE.InvoiceTemplateId
,Name= SOURCE.Name
,DetailTotalUnitsQty= SOURCE.DetailTotalUnitsQty
,InsertedDate= SOURCE.InsertedDate
,UpdatedDate= SOURCE.UpdatedDate
,Distributor_Id= SOURCE.Distributor_Id
,InsertedBy= SOURCE.InsertedBy
,UpdatedBy= SOURCE.UpdatedBy

I need a string Like below:

Name= SOURCE.Name
,DetailTotalUnitsQty= SOURCE.DetailTotalUnitsQty
,InsertedDate= SOURCE.InsertedDate
,UpdatedDate= SOURCE.UpdatedDate
,Distributor_Id= SOURCE.Distributor_Id
,InsertedBy= SOURCE.InsertedBy
,UpdatedBy= SOURCE.UpdatedBy

So I need every thing except the First value before first comma .

View 3 Replies View Related

SQL Server 2012 :: Update A Column Using Value Of Another Column

Sep 9, 2015

I have a student table like this studentid, schoolID, previousschoolid, gradelevel.

I would like to load this table every day from student system.

During the year, the student could change schoolid, whenever there is a change, I would put current records schoolid to the previous schoolid column, and set the schoolid as the newschoolid from student system.

My question in my merge statement something like below

Merge into student st
using (select * from InputStudent ins)
on st.id=ins.studentid

When matched then update

set st.schoolid=ins.schoolid
, st.previouschoolid= case when (st.schoolid<>ins.schoolid) then st.schoolid
else st.previouschoolid
end
, st.grade_level=ins.grade_level
;

My question is since schoolid is et at the first line of set statement, will the second line still catch what is the previous schoolid?

View 4 Replies View Related

SQL Server 2012 :: More Than 1 Alphanumeric Chars In A String

Mar 12, 2014

I have an employee table

Surname GivenName
ABC x.yz
A.BC X.*YZ
A*.BC xyz

The query needs to get me the surname and givenname which have more than 1 non alphanumeric characters. In this case the output will be

a.bc x.*yz
a*.BC xyz

I know how to find non alphanumeric chars in a string but not sure if I can how to find the strings with minimum 2 non alphanumeric chars?

View 9 Replies View Related

SQL Server 2012 :: Find Numbers From A String

Apr 8, 2014

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.

View 8 Replies View Related

SQL Server 2012 :: Convert Parts Of String To Int

May 2, 2014

I have log files that I am loading into a table with duration data in the format "xx hrs xx min xx sec". Only the parts that are required will be there so if duration is only 2 seconds , the column will show "2 sec".

I am trying to get the duration into in to do some analysis on it and I have come up with this query so far which returns the correct data but i am wondering if there is a way to do what I am trying in a more readable format.

CREATE TABLE #tmp(duration VARCHAR(20))

INSERT INTO #tmp
VALUES ('1 hrs 20 min 12 sec'), --4812 sec
('48 sec'), --48 sec
('39 min 1 sec'), --2341 sec
('11 hrs 1 min 1 sec'), --39661 sec
('59 min 0 sec'), --3540 sec
('2 min 50 sec') --170 sec

And this is what I have so far

SELECT CASE
WHEN CHARINDEX('hrs', duration, 1) <> 0 THEN CAST(SUBSTRING(duration, 1, CHARINDEX(' hrs', duration, 1) - 1)AS int) * 3600 + CAST(SUBSTRING(duration, CHARINDEX(' hrs', duration, 1) + 5, CHARINDEX(' min', duration, 1) - 7)AS int) * 60 + CAST(SUBSTRING(duration, CHARINDEX(' min', duration, 1) + 5, CHARINDEX(' sec', duration, 1) - 14)AS int)
WHEN CHARINDEX('hrs', duration, 1) = 0

[Code] ...

I ultimately plan on converting this to a SSIS expression so that is why I am looking to simplify it.

View 3 Replies View Related

SQL Server 2012 :: Extracting String Between Certain Characters

Aug 18, 2014

I need extracting string that is between certain characters that are in certain position.

Here is the DDL:

DROP TABLE [dbo].[StoreNumberTest]
CREATE TABLE [dbo].[StoreNumberTest](
[StoreNumber] [varchar](50) NULL,
[StoreNumberParsed] [varchar](50) NULL)
INSERT INTO [dbo].[StoreNumberTest]

[Code] ....

What I need to accomplish is to extract the string that is between the third and fifth '-' (dash) and insert it into the StoreNumberParsed while eliminating the fourth dash.

Sample output would be:

KY117
CA132
OH174
MD163
FL191

I know that parse, charindex, patindex all might come in play, but not sure how to construct the statement.

View 5 Replies View Related

SQL Server 2012 :: Group By Part Of String

Jan 8, 2015

I am trying to make a query that will group my errors messages together - my problem is that each of the error messages is unique, due to them having an unique id in them.

"GROUP BY LIKE '%ThePartToGroupBy%'"

View 9 Replies View Related







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