I'm attempting to use T-SQL to strictly parse/pull Names from a string field like such: CN=John Doe,OU=xyz,DC=ituy,DC=qwer,DC=org...I would like the ultimate result to be JUST the full name John Doe (pretty much everything after the first = sign and before the first comma. I'm attempting combinations of REPLACE, STUFF, PATINDEX and SUBSTRING, but to no avail.
View 5 RepliesWhat are some good strategic approaches to using freeform text fields fordata that needs to be queried? We have a product whose tables we can'tchange, and I need to count on a "description" field for storing a value.Two, actually. I'm thinking of adopting this convention:InvoiceNumber@VendorAcronymThere'd be a lot of vendors.Additional issue: sometimes these values would be referred to in thedescription field, and I'd need to distinguish them as referrals ratherthan as original recorded instances of the values. For that, I imaginedeither:InvoiceNumber@@VendorAcronymorInvoiceNumber&VendorAcronymInvoiceNumber//VendorAcronymetc. -- something like that.I'm just wondering if there's best practice for doing anything this stupid(hey, I'm stuck with this as our only option just now; hopefully it's onlytemporary). How to parse out whatever I end up implementing -- well, itneeds to be tractable.Thoughts?--Scott
View 21 Replies View RelatedHi,I have a varchar column with values like:000 BNP=Item one HOP=Item two LLT=Item three001 LLT=Item one002 BNP=Item one LLT=Item twoI want to parse this rows into a table similar to the following output:000 BNP=Item one000 HOP=Item two000 LLT=ItemThree001 LLT=Item one002 BNP=Item one002 LLT=Item twoHas anybody done a similar assignment? Can you share me your solution?Thank you much!Edgar J.
View 4 Replies View RelatedHello smart SQL people,
I have a field in the DB that contains name value pairs in the form of csv. Is there a neat trick (using a stored proc) to get it to be diplayed as in columns (maybe create a view around it or somthing?) ?
EX.
Select 'name1=val1;name2=val2;name3=val3' as MyData
--> Returns
MyData
-----------
name1=val1;name2=val2;name3=val3
============================================
How can i get it to return
name1 name2 name3
--------------------------------
val1 val2 val3
There is a column in a table that has values like '23 + 45 + 63', '2 + 54 - 22'... and so on. I want to get the result of this formula as float like 131, 34... and so on. Is that possible with a SELECT statement. I tried:
SELECT CAST('23 + 45 + 63' AS FLOAT)
Didn't work.
I have data in SQLSever 2008 R2 in one column as following. I would like to run a sql statement and capture them into individual columns. Would that be possible? The column separator is |
MSH|^~&|HL7ADM|PYXIS|PAH|HL7PRX_PAH|201208131129 46||ZPM^ZPM|9442|P|2.3.1||||||| ZPM|C|console|N-HEART-ST|1|23|43025204|DOBUTAMINE 1000MCG/ML|U|1|1|1|CS3808|TEST, MONKEY|||1|||||0|1|0|20120813112839||||||||0||IV|| |||||||||||
Hello everyone,
I have a package that extracts data from a Flat File. If any errors or truncation occur during the extraction of the input data, the package should fail. All fields that have erroneous values should be reported in the log file.
My Solution:
- I have created a Data Flow Task that contains a Flat File Source Adapter and a dummy destination.
- I have left the default "Error Output" configuration of the Flat File Source adapter, namely if a truncation or an error occur for a certain column, then the reaction is "Fail Component".
Problem:
This configuration gives me only the first erroneous column in the row being processed.
Question:
Is it possible to make the Flat File Source adapter continue parsing the current row before it fails? This way, I would be able to get all the erroneous columns in the row in one shot.
Thanks in advance...
Samar
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
Having an issue getting this done. The datatype in the table is varchar(50). The values in the table are
11122015
11122015
11122015
Nothing has worked so far.
I have a varchar(512) data type on my INVOICE_DATE field and it is in the following format DD/MM/YYYY.
I need it to be in MM/DD/YYYY and have tried with no luck.
SELECT CONVERT(VARCHAR(512), INVOICE_DATE, 101) AS [MM/DD/YY]
FROM F0AInvoices_Tags
This does not swap the numbers as I would have hoped for reporting purposes.
I have a table that imported as varchar. Most of these columns need to be in a numerical format. How can I convert a table with columns named column0 (needs to be int),column1 (stays varchar), column2(needs to be int), and column 3(needs to be int)?
View 4 Replies View RelatedHi all,
I'm new at this SSIS but have been able to successfully create some simple packages. My situation is that at work we use a column to describe a status of applications. However, this makes for hellacious query because some of those statuses inherintly were one or more statuses previously. Example
Admit = Admit
Accept = Admit then Accept
Withdraw Accept = Admit, Accept, then Withdraw
Decline = Admit then Decline
As you can see inherintly those were all admits at one point. So what I'd like to do is instead of having long queries for example to get all my "Admits", I'd rather query another table that has the following columns as bits:
Admit
Accept
Withdraw
That way I can query the admit column and get all my admits. How can I use SSIS to transform my "Decision" column into those bit columns?
Thanks for any help or suggestions you have.
I created a procedure called 'Longtextprocedure'. The length of this procedure is 650000 characters long. When I was giving the following query - select len(routine_definition) from INFORMATION_SCHEMA.routines where ROUTINE_NAME = 'Longextprocedure'; Length is showing as '4000' characters long. It is not showing the remaining part of the procedure, it is showing upto the 4000 characters of procedure code. But when you execute the procedure(exec 'Longtextprocedure') it is showing the exact result.
My question is i want to read all the 650000 characters long procedure code{select routine_definition from INFORMATION_SCHEMA.routines where ROUTINE_NAME = 'Longextprocedure'} to a variable which is varchar(max).
When i am trying to read the whole procedure code it is taking 4000 characters of data into the variable not the whole. Is there a way to read the huge procedure code into the variable?
Code:-
CREATE PROCEDURE FAKEPROCEDURE @procName VARCHAR(50) --@procName is 'Longtextprocedure'
AS
BEGIN
DECLARE @routineDefinition VARCHAR(MAX);
DECLARE @replaceToChar VARCHAR(MAX);
[Code] ....
When i am trying to execute the line EXEC(@dupliacteRouteDef), it is showing like there is no procedure defined. This is due to '@dupliacteRouteDef' variable is having 4000 characters of data not the whole data. Is there a way to read whole procedure data into the variable irrespective of the length of the string?
how to convert -2.0120 to -2 or 3,6789 as 3 ignoring the decimal places.
The input -2.0120 is a varchar.
I have a table with number and varchar columns. The last insert statement has 1 inserted.
The select statement should retrieve
a b
1 1
CREATE TABLE [dbo].[test1](
[a] [int] NULL,
[b] [varchar](10) NULL
) ON [PRIMARY]
insert into test1 values (1,'a')
insert into test1 values (2,'b')
insert into test1 values (4,'d')
insert into test1 values (12,'x')
insert into test1 values (15,NULL)
insert into test1 values (1,1)
I have a column in table which stores the actual signature from the singature pad. In the table it is stored as varchar(max) datatype and sample data looks like below. Below data is the actual signature of the person.
CREATE
TABLE #test
([Signature]
VARCHAR(MAX))
[code]...
How do i decrypt this data so i can print actual signature on my report.
There is a column named Timings in HH:MM:SS format. Datatype of this column is varchar(50).
I want to sum the rows in this column and get the output as one single record.
00:01:06
00:01:16
00:01:04
00:01:24
00:01:13
00:01:06
00:02:21
00:01:16
In t-sql 2012, data is obtained from [Inputtb].lockCombo1 where it is defined as varchar(8). The data is copied to test.dbo.LockCombination.combo where the field is defined as varchar(8). This copies all the data except the last right column.
Basically a value that is '12-34-56' intially from [Inputtb].lockCombo1 ends on in st.dbo.LockCombination.combo looking like
'12-34-5'. In this case the last value of '6' is missing. I have tried to use various string functions to obtain the entire value that should be '12-34-56' and ends up looking like '12-34-5'.
Here are 2 sqls that I have used and I get the same results:
1.
UPDATE LKC
SET LKC.combo = lockCombo1
FROM [Inputtb] A
JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type and A.schoolnumber =
@SchoolNumber
JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber =
[Code] ....
I can not change the definition of the columns since these are production settings.
Thus can you should me modified sql that will end up with the entire value of 8 characters in the [Inputtb].lockCombo1 column?
I have written below query.
-- AssignedToTasks 32,'from (Select Distinct wft.*','
OVER(Order by task.TaskId desc)','','where IsActive=1 AND RecurrenceRule is null',0,5
Alter PROCEDURE [dbo].[AssignedToTasks]
@UserId varchar(15) ,
@selectClause Nvarchar(max),
[Code] ....
Hi guys,
Is there a way to declare a default value of empty string '' for a varchar table column?
Thanks,Kevin
I have a table on two different servers, the only difference that I can see is that on server A columns first (varchar 32) and last (varchar 32) have ANSI_PADDING set ON and on server B those columns are OFF. No idea why this is true: I didn't specify that the table be set up this way and they both followed similar creation/upgrade paths.
I execute "select last+first from <table>" on server A and the result looks like:
<last1> <first1>
<last2> <first2>
...
On server B I get
<last1><first1>
<last2><first2>
Now the docs say ANSI_PADDING has nothing to do with this behavior; in fact if I copy the data on server B to 2 new columns with ANSI_PADDING ON I get the same results. But that's the *only* thing that was different in syscolumns. What is causing the different output behaviors on these two servers? Thanks.
I have an application with highly compressable strings (gzip encodingusually does somewhere between 20-50X reduction.) My base 350MBdatabase is mostly made up of these slowly (or even static) strings. Iwould like to compress these so that my disk I/O and memory footprintis greatly reduced.Some databases have the ability to provide a compressedtable, compressed column, or provide a user defined function tocompress an indvidual Field with a user defined function[ala. COMPRESS() and DECOMPRESS() ].I could right a UDF with an extended prodcedure if I need to but I'mwondering if there are any other known methods to do this in MS SQLServer 2000 today?--Frederick Staatsfrederick dot w dot staats at intel dot com (I hate junk mail :-)
View 6 Replies View Related
This is obviously a radical idea but some actually DO want to store linefeeds in varchar columns.
In MySQL I can escape difficult characters for example
INSERT INTO sometable(address) VALUES("23 SomeRoad
SomeTown
SomeCounty");
Does anyone know how to do this in Transact SQL?
I have one of the sample values in my table. I need to convert below value to Decimal(18,5)
DECLARE @i
VARCHAR
SET @i
= '0.9'
Output i m looking for is 0.90000
We have a customer that is running SQL2012 and we are seeing a weird result on a query when we run it on their db. It is based off of a table that has about 30 columns but in this case we only care about 2 of them.
[Number] [varchar](15) NOT NULL
[Person_ID] [varchar](12) NULL
Here is the query we are doing:
Select Number,Person_ID From TableName where LP='ABC123'
The result I get back is the following:
Number:1
Person_ID:13864
The Person_ID should be a result of another table that created that Person_ID but it doesn't exist in that table. So we do not know where that 13864 is coming from. When we open that record through our application it shows Nothing for the Person_ID in that field.
When we do this query on our copy we get back
Number:1
Person_ID:
Which is exactly what we should see as the result.
Could there be a sql server setting that is set on their server that could possibly be given us back 13864 for a NULL value?
Hi,This is probably an easy question for someone so any help would beappreciated.I have changed the columns in a table that where nvarchar to the samesize of type varchar so halve the space needed for them.I have done this a) becuase this is never going to be an internationalapplication, b) we are running out of space and c) there are 100million rows.I have done this with the alter table statement which seems to work butthe space used in the database hasn't altered.I'm presuming that the way the records are structured within the tablethere is just now more space free inbetween each page???Is there a way or re-shrinking just an individual table and free upsome of the space in there or am i missing the point somewhere?Thanks in advance,Ian
View 4 Replies View RelatedI have an encrypted column of data that is encrypted by a passphrase. The passphrase was encrypted by a symetric key in a key pair. The passphrase also is stored in a table. I can get the passphrase as needed to encrypt/decrypt the columns. I copied the production database to a new database for development. Subsequently I had to create a new symmetric/asymmetic key pair and recreated my passphrase with the new key pair. Now the passphrase will decrypt a text column but it will not decrypt two other columns which are of type varchar in the database. Here is an example:
DECLARE @pss varchar(30)
EXEC [dbo].[uspPassPhraseGet] @pss OUTPUT
SELECT DISTINCT contactid, uissueid, createdby, created_dt
,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.title), 1, CONVERT(varbinary, 23))) as title
,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.description), 1, CONVERT(varbinary, 23))) as description
,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.shortdesc), 1, CONVERT(varbinary, 23))) as shortdesc,
closed_dt, confidential, statusid, due_dt, deleted_dt,deletedbyid, highrisk, dbo.tbl_msg_app_legislativeinquiry.designator, dbo.tbl_ref_sys_status.description AS statusdesc
FROM dbo.tbl_msg_app_legislativeinquiry INNER JOIN
dbo.tbl_ref_sys_status ON statusid = dbo.tbl_ref_sys_status.ustatusid INNER JOIN
dbo.tbl_gbl_lkp_security ON uissueid = dbo.tbl_gbl_lkp_security.msgid AND
dbo.tbl_msg_app_legislativeinquiry.designator = dbo.tbl_gbl_lkp_security.designator
Like I said I can execute the uspPassPhraseGet stored procedure and I get my passphrase. It will correctly decrypt the dbo.tbl_msg_app_legislativeinquiry.description field which is great but the other two fields will not decrypt. When i copied the database over the encrypted fields do not display the same on the new database. The old database shows a box character followed by a bunch of junk (as expected). The new copied table on the new database shows only a single box (not the same as the original). Is there a known bug with copying a table with varchar fields that are encrypted to a new database? I tried to run a test and got the same result. I also tried to convert the varchar columns to text to see if that solved the problem and it didn't. The description field however is a text type column and it reads exactly as the original. The problem I think is that the Copy Database didn't actually copy my data correctly. How can I get the original encrypted data from the production into my development. I also tried just dropping the table and reimporting the table but that didnt take either. Scratching my head on this one.
CASE WHEN NULLIF(NHSNo2, '') IS NULL THEN 1
WHEN NULLIF(Surname, '') IS NULL THEN 2
WHEN NULLIF(Forename, '') IS NULL THEN 3
WHEN NULLIF(DOB, '') IS NULL OR DOB < '01/01/1900' THEN 4
WHEN NULLIF(AddressLine1, '') IS NULL THEN 5
[code]...
The above code worth great but ideally instead of returning a number Id like to return text for example
1 = NHS
2= SUR
3=FOR
4=DOB
5=ADD
6=PCO
7=GPN
8=PCZ
9=GPD
10=CCG
Below is garbage data and structure (I think enough to get point across). How can I perform calculations needed?
Create Table #1234
(
abcd decimal(16,4)
,defg decimal(16,4)
,hijk decimal(16,4)
,logon datetime
,logoff datetime
[code]....
DECLARE @i BIGINT
SET @i = 20150315
DECLARE @S VARCHAR(MAX)
SET @S = ''
SELECT @S = @S + '
DECLARE @Count BIGINT
SET @Count = '+@i+' + 1
SELECT @Count'
EXEC(@S)
I am trying to execute the above query but it is throwing me an error.
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to bigint.
if I 'print' a MONEY value, or cast a MONEY variable to VARCHAR, it automatically rounds it to two decimal places. Maybe that's a built-in convenience, but I'd like to make it not do that.My workaround right now is to first cast my MONEY variabled to DECIMAL(30,4), and then cast the result to VARCHAR, but I'd like to avoid that step if possible.Consider the following
query:DECLARE @UnitCost MONEY SET @UnitCost = .0167 SELECT @UnitCost,
CAST(@UnitCost AS VARCHAR(30)),
CAST(CAST(@UnitCost AS DECIMAL(30, 4)) AS VARCHAR(30)) -
- Results in: 0.0167, 0.02, 0.0167
i am trying to run to get a list of computers from a table based on the available free space. I'd like to group them based on the available space, for example, up to 1 GB, 1-2 GB, etc.
With Disk_Space (ResourceID, ComputerName, Description, DiskName, VolumeName, FileSystem, Size, FreeSpace)AS
(
SELECT distinct
SYS.ResourceID,
SYS.Name,
LDISK.Description0,
LDISK.DeviceID0,
LDISK.VolumeName0,
LDISK.FileSystem0,
[code]...
However when i run this query, i get an error :
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '1GB To 2GB' to data type int.
I know that if I have an nvarchar column I can use an equality like = N'supersqlstring' so it doesn't implicit cast as a varchar, like if I were to do ='supersqlstring'. And then I'll be a big SQL hero and all my stored procedures will run before a millisecond can whisper.
But if I'm comparing an nvarchar column to a varchar column, is it better to cast the varchar 'up' to an nvarchar or cast the nvarchar 'down' to a varchar?
For instance:
cast(a.varchar as nvarchar(100)) = an.nvarchar
or
cast(an.nvarchar as varchar(100)) = a.varchar
Leaving aside non-matching, like (at least I don't think) that SQL considers the varchar n to be equal to the nvarchar ń, what's the best way to handle this?
Pretend for a moment that each column contains a mixed letter and number ID with no accented or wiggly-squiggly Unicode characters; it's just designs clashing.
Is there a performance hitch doing it one way or another? Should I use COLLATE? Should one of the columns be altered?