SQL 2012 :: Remove Character From Specific Column
Nov 18, 2014How to remove Character From Column,Just Character. I want to work with number.
View 4 Replies
ADVERTISEMENT
Remove Invalid Character From TXT File Value In Copy Column Transformation....!
Dec 6, 2007In DTS/SSIS packages, How do to data validation or check for special characters and remove them at Copy column level.
thanks,
Reporting Services :: Remove Duplicates Based On A Specific Column
Jun 22, 2015I have some duplicate values for my query results, about 200 duplicates out of 30000 rows. Of these 200 duplicates I want to keep the ones that have a higher value for... 'UpdatedBatchID'.
SELECT
IR.Id as 'ID'
, CAST(IR.Priority as varchar) as 'Priority'
, IRSupportGroupDN.DisplayName as 'Support Group'
, DATEADD(MI,DATEDIFF(mi,GETUTCDATE(),GETDATE()),IR.CreatedDate) as 'Created Date'
, DATEADD(MI,DATEDIFF(mi,GETUTCDATE(),GETDATE()),IR.ResolvedDate) as 'Resolved Date'
, SLOConfig.DisplayName as 'SLO'
, DATEADD(MI,DATEDIFF(mi,GETUTCDATE(),GETDATE()),SLOFact.TargetEndDate) as 'SLO Target'
, SLOStatusDN.DisplayName as 'SLO Status'
, SLOMetric.DisplayName as 'SLO Metric'
, SLOFact.UpdatedBatchId as 'UpdatedBatchID'
SQL Server 2012 :: Select Case Statement To Remove Part Of String After One Or Two Specific Characters
Jun 3, 2015I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'
Example Record 1. Elland **REQUIRES BOOKING IN***
Example Record 2. Theale, Nr Reading, Berkshire
Example Record 3. Stockport
How do I achieve this in a CASE Statement?
The following two case statements return the correct results, but I some how need to combine them into a single Statement?
,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0
THEN LEN(Address )
ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'
,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0
THEN LEN(Address)
ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'
SQL 2012 :: Selecting From Linked DB2 Server / Specific Column
May 13, 2014I've got an OLEDB DB2 linked server to a db2/AS400 instance and selecting from a table on the server has never caused problems before. One of the columns is a large text field. If I select all the columns but the large text field, it returns as normal, but including the large text field now, I get:
"Transport error: shared memory provider error: 0 - no process is on the other end of the pipe"
The largest entry in the text field is about 5k characters, and there don't appear to be any strange characters.
SQL Server 2012 :: Remove Non Alpha Chars From A Column
Mar 1, 2014How to remove non alpha chars from a column ?
I googled the following code
Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin
[code]....
SQL Server 2012 :: Remove CR-LF In Between Column Which Is Text Datatype?
May 1, 2015I have a column A as 'text' datatype. This has CR-LF in between data.How to remove it.
I am trying to copy result from SQL to Excel
Eg:
A CR LF
ABC CR LF
DVB CR LF
The output should be:
A
ABC DVB
SQL Server 2012 :: Remove String From Column Post Code Field
Sep 8, 2015I 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?
Select Specific Character
Dec 15, 2014How to select specific character when you have a different length of characters. For example I have a columm where I have character ":". How to select only ":"?
View 2 Replies View RelatedReplace Specific Character
Aug 14, 2007I have to replace 'MED-40'instead of '30' in an incidentno column of incidents table ,the fiels is varchar(12),now the problem is it is replacing whereever it sees '30' while I want only the leftmost to be replaced for eg
Let '301530' be the incidentno to be replaced
if I run
UPDATE incidents_mm set incidentno = replace(incidentno, '30', 'MD-40')where incidentno= '301530'
query i get the ans given below
'MD-4015MD-40'
and I just want the ans to be
MD-401530
In short i just want first 2 characters to be replaced is there any way i can do that
Thanks in adv
Transact SQL :: Select Specific Values From All Rows Where Value Of A Specific Column Is (Active)
May 23, 2015I 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.
Get Specific Range Of Character Through Query
Mar 19, 2008Hi !
i have a query like select top 3 column1 from table 1 order by Id desc
here the column1 is of varchar(512) data type but i want to get only first 250 chars of it and if they are less than 250 then get the whole range
plz let me know how it is possible throguh query or any other way
Regards,
T-SQL (SS2K8) :: Remove All Characters After 3rd Repetition Of A Character?
Sep 19, 2014best possible way to remove all the characters after a 3rd repetition of a character?
For Example:
I want 10.0.1600.22 to be 10.0.1600
Everything after and including the '.' to be removed.
I understand Substring_Index() is not available whats the other options?
Specific Invalid Character Value For Cast Specification Problem
May 25, 2007Hi, I'm a new DBA and am a rookie when it comes to working with SQL Server 2005. I have been tasked with transfering an Access database to SQL Server 2005. Now I understand to use the import/export wizard, but am having problems fixing an error I am receiving. Here is the error...
Problem I
- Copying to [IS_Shared_ServicesTest].[dbo].[tblResource] (Error)
Messages
* Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
(SQL Server Import and Export Wizard)
* Error 0xc020901c: Data Flow Task: There was an error with input column "Contract Start Date" (115) on input "Destination Input" (80). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
(SQL Server Import and Export Wizard)
Here is the short version of the error. I realize that there is an error with the conversion and the error is in tblResource in column Contract Start Date. It is still transfering the column over, and on spot check they seem fine. Should I be really worried about an error? Or is there something that I can do with the Access or SQL server database to fix this?
Reporting Services :: How To Remove Line Character In Stacked Bar Chart
Sep 21, 2015I have a stacked bar chart which i notice a vertical line char. how can i removed this vertical line? I already modified the properties under smart labels under calloutlineanchor, calloutlinestyle, calloutstyle to None but still getting this vertical lines. another things is the data label as you can see in the the first column the 1.8% should be place under the gray color but its already shown before the orange color.
View 3 Replies View RelatedRead Chinese Character From SQL(SQL Server 2005) Database Table Column And Display Chinese Character
Feb 1, 2008Hi!
I have a table like this below and it doesn't only contain English Names but it also contain Chinese Name.
CREATE TABLE Names
(FirstName NVARCHAR (50),
LastName NVARCHAR (50));
I tried to view the column using SQL Query Analyzer, It didn't display Chinese Character.
I know that SQL Server 2005 is using UCS-2 Encoding and Chinese Character uses Double Byte Character Set (DBCS) Encoding.
I want to read the FirstName and LastName columns and display in Window Form Data Grid and ASP.NET Grid View.
I tried to use this code below and it didn't work. It convert some of the English Name to Chinese Character and it display the chinese character and some still in the original unreadable characters.
Does anybody know how to read those character from SQL Table and display the correct Chinese Character without converting the English Name into Chinese also?
Thanks
int codePage = 950;
StringBuilder message = new StringBuilder();
Encoding targetEncoding = Encoding.GetEncoding(codePage);
byte[] encodedChars= targetEncoding.GetBytes(str);
.
message.AppendLine("Byte representation of '" + str + "' in Code Page '" + codePage + "':");
for (int i = 0; i < encodedChars.Length; i++)
{
message.Append("Byte " + i + ": " + encodedChars);
}
message.AppendLine(" RESULT : " + System.Text.Encoding.Unicode.GetString(encodedChars));
Console.Writeline(message.ToString());
SQL 2012 :: Can't Remove DB Mirroring
Oct 26, 2012I created a mirrored DB, added a new datafile to the principal using a path the mirror can't access. As a result the mirroring session was suspended because the mirror stated: "CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file".
Fine, so I disabled mirroring on the principal (SET PARTNER OFF) which worked just fine, but the mirror stayed in a mirrored state, Object Explorer saying:"(Mirror, Disconnected / In Recovery). Sooo, I tried to disable db mirroring on the mirror using ALTER DATABASE db SET PARTNER OFF;, which completed successfully, but the DB STILL remained in a mirrored-configuration.
I tried ALTER DATABASE db SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS which resulted in
"Msg 1404, Level 16, State 4, Line 1
The command failed because the database mirror is busy. Reissue the command later."
I tried taking the DB offline or restoring it from a backup, but all these operations resulted in "The operation cannot be performed on database "db" because it is involved in a database mirroring session or an availability group."
The only solution I can think of at the moment is shutting down the instance and deleting the data and log-files of the mirrored db, which would be just fine because this is just a test installation, but it would be not quite as easy in a productive environment.
Is there any other way to remove the mirrored state from a disconnected mirror or to simply get rid of the db entirely to perform a recovery?
SQL 2012 :: Database Remove From AG
May 19, 2015As part of patching i need to remove the database from availability group and add the database after patching is done , how can i achieve this ?
View 2 Replies View RelatedSQL 2012 :: Output In Specific Format
Dec 12, 2014I have sample data like 'J.S.xxxx','J.xxxx.'
Want to separate the . in the middle of word with single space and display results in following way.
J. S. xxxx
J. xxxx
Need the code in optimized way.
SQL 2012 :: Remove TempDB NDF File
Jul 8, 2014I have added ndf to tempdb for checking performance improvement.... Now I want to remove the ndf file... I am using below command...
USE tempdb
GO
DBCC SHRINKFILE (3, TRUNCATEONLY);
GO
use master
go
ALTER DATABASE TEMPDB Remove FILE tempdev1
Results:
DbIdFileIdCurrentSizeMinimumSizeUsedPagesEstimatedPages
23766476643232
Error:-
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5042, Level 16, State 1, Line 1
The file 'tempdev1' cannot be removed because it is not empty.
Note:
=>I restarted SQLServer from SSMS and then ran same commands mentioned above ,......and getting same error...
=> I executed above commands and restarted services...no change...
How to remove / drop ndf file...
SQL Server 2012 :: Remove First And Last Char
Jun 5, 2015How to remove first and Last character from below select query.
SELECT LTRIM(RTRIM(REPLACE(col_1,'Year_',''))),
FROM Years
SQL Server 2012 :: Character Value Count From Dataset
Oct 31, 2014Consider following script
create table data_set (id int primary key, col1 varchar(10))
go
insert into data_set values (1,'a'), (2,'b'),(3,'c'),(4,'d'),(5,'a'),(6,'b'),(7,'e'),(8,'f'),(9,'a'),(10,'a')
select * from data_set
I tried this below
Declare @child_ids int
@col_val varchar
@count int
select @child_ids, @col_val, @count, count(col1) as records from data_set
group by col1
order by col1
But I want my output like below .....
SQL Server 2012 :: Tab Character In Query Results
Jul 9, 2015I am getting a tab character at the end of my query fields, been trying various things to fix, such as using the replace function below but still i get the tabs!
select CAST(REPLACE(NAMEALIAS,CHAR(9),'')AS CHAR(40)) + ',' as PRODNAME,
CAST(REPLACE(ISNULL(GLOBALTRADEITEMNUMBER,0),CHAR(9),'')AS CHAR(18))as EANNO,LTRIM(cast(ISNULL(GLOBALTRADEITEMNUMBER,0) as char(18))) as KONSEAN
,LTRIM(CAST(I.ITEMID AS CHAR(8))) AS PRODCODE,'00' from INVENTTABLE I
LEFT JOIN INVENTITEMGROUPITEM IG ON I.ITEMID = IG.ITEMID
[Code] ....
Results:
Gießanzeiger groß ,04260376730475 04260376730475 730475 00
Gießanzeiger klein , 04260376730352 04260376730352 730468 00
Gießanzeiger klein , 04260376730468 04260376730468 730468 00
SQL Server 2012 :: Find First Value Greater Than Specific Value
Dec 19, 2013I need a query to get the first value greater than a specific threshold value
LoadTemp1 LoadTemp2 LoadTemp3 LoadTemp4 TimeStamp
300 200 100 320 12-10-2013 13:30:29
100 250 113 340 12-10-2013 14:20:12
114 339 209 345 12-10-2013 14:45:01
I need to get the first value >= 340 threshold
I need to make a Benchmark on when the first LoadTemp crosses Threshold of 340 and capture the time.
SQL Server 2012 :: Get Results In A Specific Order?
Sep 11, 2014I have a simple example of what I am trying to do. Here is some code to make a quick table to demonstrate:
create table #students
(
lastname varchar(50)
,firstname varchar(50)
,address1 varchar(50)
[Code].....
I want to select all the records, and them them be in alphabetical order first by lastname, then by firstname, then by address. HOWEVER, and this is the tricky part, I want to group names together that have the same address. So, in this example, I want the results to be in this order:
HallC6309 N Olive
HallP6309 N Olive <---- grouped with the C record because they have the same address
HallE5488 W Catalina <---- back to alphabetical by first name
HallJ7222 N Cocopas
SQL 2012 :: Restore Specific Columns From A Database
Dec 15, 2014I have an SQL .bak file and I would only like to restore specific columns as one of the columns is a free text field and is substantially increasing the size of the file. I can't restore it due to disk space constraints so dropping the column isn't possible if I can't get the table into a database locally.
View 1 Replies View RelatedSQL 2012 :: Using Resource Governor To Limit A Specific DB?
Jan 13, 2015I was looking for a little insight on Resource Governor. I have a request from my manager to limit the resources a certain third party's DB can use on our system. We allow them to keep a DB on our SQL 2012 server so they can run query's to compare data to their server without linking the two machines.
I was looking into resource manager to perhaps accomplish this task but I'm not real familiar with it. If Resource governor can be used to only allow a DB named UserDB1 to use only 1GB or Memory at any one time?
Or perhaps there is another way aside from Resource governor?
SQL 2012 :: JOIN If Specific Table Exists
Jun 9, 2015I have standard SQL query for clients, but sometimes a client does not have a specific table.
How do I deal with that? (creating the table is not an option)
SELECT
A.Col1
,A.Col2
,B.Col3
--> Col 50
FROM
TableA A Join
TableB B ON A.ColA = B.ColA
Suppose in some cases TabelB does not exist. How do I make sure the rest of the query runs okay and does not result in an error?
SQL 2012 :: Powershell Command To Install Specific KB
Jul 1, 2015My system is windows 2012 core, no gui installed..I need to install missing kb through powershell. Need the powershell command to install specific KB
View 1 Replies View RelatedSQL 2012 :: Query To Filter Out Specific Data?
Aug 21, 2015I need to return 1 ip address for each machine. I can easily achieve this with using group by and return max(ipaddress) however I would like to filter out specific range '192.168.%' but only want to do that if machine has more then 1 ip address. If the machine has only 1 ip address then it can return it even if its '192.168.%'
below is test table to show sample data
CREATE TABLE dbo.[testip](
[machineid] INT NULL,
[ipaddress] [varchar](20) NULL
)
GO
INSERT INTO [dbo].[testip] ([machineid],[ipaddress]) VALUES (1,'155.119.1.22')
INSERT INTO [dbo].[testip] ([machineid],[ipaddress]) VALUES (1,'192.168.1.5')
[code]....
SQL 2012 :: How To Remove Records From Repository / Alerts
Feb 18, 2014We configured IDERA SQL Safe for backups and restores.we setup an email for notifications. One day we performed manual backup operation for 150 databases from SQL Safe tool. Unnoticed it backed up to C:Backup folder.
We got alerts with the report of backups on C: drive. Then we moved backup files to respective folders.But, I could not clear the records from the report, its been 25 days, still we are receiving the alerts as below.How can I clear this report or to configure or setup anything to avoid this, in future as well.
Below is the sample records from the report. I need this report to be cleared.
Subject: SQL Safe Validation Report
The following files are recorded in the SQLSafe repository, but no longer exist in their locations:
C:BackupLUXOR_DB_GroupEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_InsightEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_IMEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_SiteEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_SiEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_ICEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_vdbEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_M_SEPF_Diff_201402050045 (1 of 1).safe
SQL 2012 :: Remove Digits From Fields Of Table
Apr 3, 2014I have table like
name
22rajuvar
45 vamsgui
87 praveen
67kumar
32 vamshi
Above field mix with digits and character. I want to remove digits. I want o/p like ............
name
rajuvar
vamsgui
praveen
kumar
vamshi
SQL 2012 :: BCP Output - Remove Milliseconds Part
Apr 18, 2014I The requirement is to unload all columns data into csv file using bcp with pipe delimiter, but the condition is to remove milliseconds part of a datetime column.
Ex:
2014-02-19 17:12:14.967 remove .967 from data while unloading into csv.