Transact SQL :: Remove Space If Exists
Oct 23, 2015
I am trying to bulk update about 50,000 rows in a SQL table. The values that the table MUST contain are:
1-3 days
4-7 days
8-10 days
Some of the rows contain a space between the number and the hyphen like:
1 - 3 days
4 - 7 days
8 - 10 days
What would be my best methodology of removing the space between numerics only? I have seen multiple examples of how to remove ALL whitespace, but I only want to remove the space between the numbers and the hyphen *IF* it exists. And the field type is varchar(200)
View 8 Replies
ADVERTISEMENT
Aug 3, 2015
I'm getting data from a flat file and there is space before few values. I'm unable to remove that space through replace or LTRIM. How to remove these spaces.For E.g.
Values
' 2356'
' 1'
' 23'
' ABCD'
View 3 Replies
View Related
Nov 26, 2015
I am using the below script to get space alerts and now i am interested in sending alerts if for any drive space available is Less than 10% or 15%.. how to convert beelow code to find in %
Declare @Drives Varchar(20)
DECLARE @Spaces Varchar(50)
DECLARE @availableSpace FLOAT
DECLARE @alertMessage Varchar(4000)
DECLARE @RecipientsList VARCHAR(4000);
CREATE TABLE #tbldiskSpace
[Code] ....
View 3 Replies
View Related
Jan 31, 2008
I have the following data example, it is 1 column named ItemCode in the table called ItemNo,
ItemCode
1000830 000830 92
1001156 001156 92
And i have the following code,
SELECT SUBSTRING(ItemCode, CHARINDEX(' ', ItemCode), CHARINDEX(' ', ItemCode)) AS 'ItemCodeTo'
FROM Cust.ItemNo
But the result has a 'space' character at the beginning. I do not need this 'space' character.
What is wrong with my code above? How to remove the space character?
ItemCodeTo
000830
001156
View 6 Replies
View Related
Aug 8, 2007
Hi
I have datacloum called 'code'. When I use @imglink+''+code+''+'.jpg' as [main-image-url],'in my stored procedure. I should get the folowing
http://www.batterymasters.co.uk/ImageResizeCache/productImages/Pack/ACCFUFN076360A.jpg
But I am getting like the one below
http://www.batterymasters.co.uk/ImageResizeCache/productImages/Pack/AABFUFN076360A. jpg
So, some of them are fine but most of them seperated by blank space. I thing the datacolum 'code' has value follwed by empty string like below
'ABFUFN076360A '
SO HOW DO I REMOVE EMPTY STRING AND GET ONLY 'ABFUFN076360A' instead of 'ABFUFN076360A ' in my STORED PROCEDURE.
Please help
View 2 Replies
View Related
Sep 20, 2006
Hello
When you set the results from a query to text, very often you end up with a format like:
job_id name
---------------------------------------------------------------------- ------------------------------------------------------------------------------------------------
Is there a way to get rid of the extra space between two columns? Sometimes it's more than the width of my screen between columns
Thanks!
Worf
View 4 Replies
View Related
Nov 18, 2015
I am using SQL Server 2008 - and what I want to do is set my variable @dh. If the @startDate and @endDate falls into the criteria for my if exists statement, I want to set @dh equal to datediff(h, logontime, logofftime) BUT if that criteria is not true, I want to set @dh = 24. How can I do that?
Declare @startDate date, @endDate date, @employeeID varchar(100), @userid varchar(100), @dh int
Set @startDate = '11/09/2015'
Set @endDate = '11/14/2015'
Set @employeeID = 'ab12345'
Set @userid = '162489'
[Code] .....
View 3 Replies
View Related
Oct 2, 2015
This is my query, from my nonono table I need to return the most recent date from attempted (column name) How could I modify this to do such
Select *
FROM [nonono] td
WHERE (NOT EXISTS
(SELECT [First], [second]
FROM [yesyesyes] AS d
WHERE ([First] = td.[First]) AND ([second] = td.[second]) AND ([Worksite] = td.[Worksite])))
View 3 Replies
View Related
Feb 18, 2010
I am trying to determine the existence of at least one row in my Detail table using EXISTS in my SELECT list from my Main table.SELECT M.ID,EXISTS(SELECT 1 FROM Detail D WHERE D.ID = M.ID) as HasDataFROM Main MCan this be done this way?I was hoping that using EXISTS would find a row and move on thus increasing performance.
View 15 Replies
View Related
Aug 4, 2015
When i am running below snippet execution plan is showing constant scan instead of referring subquery table.
I want to know how this query working. and why in execution plan there is no scan /seek which will basically indicate that particular table is getting referred.
select count(*) from A where exists (select count(1) from B where A.a=B.a)
execution plan has to show scan or seek for subquery. Surprisingly, output is coming as expected.
View 8 Replies
View Related
May 25, 2015
I have a table which has 2 columns and the data is like below
API_Number Group_Name
1234 Group A
3241 Group A
1234 Group B
4567 Group C
7896 Group D
3241 Group E
I wanted to find the API numbers which are repeating in different groups. In the output I want
API_Number Group_Name
1234 Group A,Group B
3241 Group A,Group E
View 5 Replies
View Related
Jan 21, 2010
What is the best way to drop a temp table if it exists? I am looking something similar to this: if exists (select top 1 * from #TableName) then drop #TableName else end
View 5 Replies
View Related
May 6, 2015
I have been asked to create PK on many tables using a query on all tables where we do not have clustered indexes. Some of the tables contains PK but non-clustered. If in a table there are no PK, then how to decide on which column PK can be created? can we do it with the query without data loss and without human intervention?
View 5 Replies
View Related
Nov 25, 2015
i had a query for generating full address from add1,add2,add3 fields.
declare @temp table
(ADDR1 varchar(10),
ADDR2 varchar(10),
ADDR3 varchar(10)
[code]....
but still i am getting ',' for second record as u seen when ADD1 is null.so how can i remove that comma','
View 3 Replies
View Related
Jul 23, 2015
When I execute the below queries it works perfectly where as my expectation is, it should break.
Select * from ChildDepartment C where C.ParentId IN (Select Id from TestDepartment where DeptId = 1)
In TestDepartment table, I do not have ID column. However the select in sub query works as ID column exists in ChildDepartment. If I do change the query to something below then definately it will break -
Select * from ChildDepartment C where C.ParentId IN (Select D.Id from TestDepartment D where D.DeptId = 1)
Shouldn't the default behavior be otherwise? It should throw error if column doesnt exists in sub query table and force me to define the correct source table or alias name.
create table TestDepartment
(
DeptId int identity(1,1) primary key,
name varchar(50)
)
create table ChildDepartment
(
Id int identity(1,1) primary key,
[Code] ....
View 3 Replies
View Related
Jul 31, 2015
I have a table that has for each shop a value that can change over time.For example
BK_POS 1 --> Segment A
BK_POS 1 --> Segment /
What I would like to achieve is to get all distinct Shops (BK_POS) from the table above, but if for that specific pos a row exists where the segment = "/" then I do not want to take this BK_POS in my select query.More concrete, the for example above I do not want to select BK_POS 1 because he has one row where the segment = "/".
View 3 Replies
View Related
Oct 29, 2015
Usual way to check if file exists
DECLARE @File_Exists INT
EXEC Master.dbo.xp_fileexist 'serverBSQL_Backupfile.bak', @File_Exists OUT
print @File_Exists
1
And if check folder, can use "nul", but it doesn't work for UNC path
DECLARE @File_Exists INT
EXEC Master.dbo.xp_fileexist 'serverBSQL_Backup
ul', @File_Exists OUT
print @File_Exists
0
If use xp_subdirs like:
EXEC master.dbo.xp_subdirs 'serverBSQL_Backups'
If the folder doesn't exists,
Msg 22006, Level 16, State 1, Line 3
xp_subdirs could not access 'ServerBSQL_Backups*.*': FindFirstFile() returned error 67, 'The network name cannot be found.'
How to check if UNC folder exists in Backup? in my code I want to check if the unc folder exists before doing backup, the unc path is retrieved from other table or backup history.
View 8 Replies
View Related
Sep 25, 2015
I have a table contain ProductMaster and it is using in multiple table like sales,purchase,inventory,production and many of other tables. i want delete all product which are not using in any child table.
View 8 Replies
View Related
Aug 26, 2011
i have a data base sql Server .I wrote a stored procedure or attach my database but it is attached in read only mode how can remove read-only.
this is my stored procedure.
create procedure attache
as
declare @trouvemdf int
declare @trouveldf int
if exists (select name from sysdatabases where name='Gestion_Parc')
[Code] ....
i try this EXEC sp_dboption 'Gestion_Parc', 'read only', 'FALSE' but it causes those error
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C: Gestion_Parc.mdf". Operating system error 5: "5 (Access is denied.)".
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C: Gestion_Parc_log.ldf". Operating system error 5: "5 (Access is denied.)".
Msg 945, Level 14, State 2, Line 1
[Code] ....
View 3 Replies
View Related
Nov 16, 2015
I have scenario where i have to pick one particular value from where condition. Here is the example:A store can have different types i-e A or B , A and B or either A or B.
Store Type Sales
11 A 1000
23 A 1980
23 B 50
5 B 560
I want to filter the store in "where clause" where
1)- if the store has type A and B, then assign only A
2)- if the store has type A associated with it then assign A
3)- if the store has type B associated with it, then assign B.
Select Store, sum(sales), Type
from table1
where (TYPE]= (case when [TYPE] in ('A','B') then 'A'
when [TYPE]='A' then 'A' else 'B'end))
GROUP BY [store], [TYPE]
The above statement is not working for when store has only Type B associated with it.
View 7 Replies
View Related
Jul 1, 2015
example:
column: ID numbercar before ID numbercar after
1 00122011 1 122011
2 00042010 2 42010
3 03102012 3 3102012
View 5 Replies
View Related
Aug 31, 2015
The table I have is:
CREATE TABLE [dbo].[FTE2015](
[Firm Number] [varchar](50) NULL,
[w9] [varchar](50) NULL
) ON [PRIMARY]
GO
select * from dbo.FTE2015
Firm Number w9
709485"" 0
040898A" 12.5
709502"" 2.4
041382"" 0.4
709503"" 0.3
709681"" 4.9
How do I remove the trailing blanks? I tried RTRIM but it does not work.
SELECT RTRIM([Firm Number])
FROM dbo.FTE2015;
(No column name)
709485""
040898A"
709502""
041382""
709503""
How can I resolve this? The [Firm Number]column is not of a fixed length.
View 11 Replies
View Related
Apr 17, 2012
I have a table for example like following
DECLARE @tmpTable table
(
name varchar(10),
address1 varchar(10),
phnno varchar(10),
mobno varchar(10)
)
INSERT INTO @tmpTable(name,address1,phnno,mobno)
[Code] ....
I want to remove all empty rows like row 1,2 and 3 in the above example.
I can't check all columns null values as there are many columns in my actual table.
View 6 Replies
View Related
Dec 3, 2010
I have a table that "Geography" that has the following columns: city, state, zip
There are tons of duplicate cities in this table. I ran this query and it shows me the number of occurrences of each city. I want to delete all the duplicates except for 1. I don't want to do this manually as there are a lot of records.
What would the SQL look like to delete the duplicate records but keep at least one?
View 9 Replies
View Related
Sep 1, 2015
I have table with columns as ID, DupeID1, DupeID2. ID column is unique. DupeID1 and DupeID2 -- the combination should only be there once. I don't want reverse combination of duplicates, i.e. DupeID2, DupeID1 in the table. How can I delete the reverse duplicates from this table?
View 10 Replies
View Related
Oct 14, 2015
I have a table that holds full names but it's lastname, firstname.I need to select and convert to firstname lastname and remove the comma.
View 7 Replies
View Related
Oct 25, 2012
I'm not sure about why I'm not able to remove spaces even after trimming them. Below is the result of query I'm usning.
select distinct LTRIM(RTRIM(Promotion_Code)) Promotion_Code
--, count(Promotion_code)
from dbo.Marketing_Promotion_Tb
where Promotion_code like '%1BTPIZZA%'
Result :
Promotion_Code Length
1BTPIZZA 10
1BTPIZZA 8
View 12 Replies
View Related
May 18, 2015
DECLARE @FullName VARCHAR(100)
SET @FullName = 'Vauxhall Adam Rocks AIR Vauxhall'
SELECT LEFT(@FullName, NULLIF(CHARINDEX(' ', @FullName) -1, -1)) AS [FirstName],
RIGHT(@FullName, ISNULL(NULLIF(CHARINDEX(' ', REVERSE(@FullName)) - 1, -1), LEN(@FullName))) AS [LastName]
This is only gives first and last not first and middle
DECLARE @FullName VARCHAR(100)
SET @FullName = 'Vauxhall Adam Rocks AIR Vauxhall'
SELECT
STUFF(@FullName,charindex(' ',SUBSTRING(@FullName,5,LEN(@FullName)))+5,LEN(@FullName),'') [Firstname1],
STUFF(@FullName,1,charindex(' ',SUBSTRING(@FullName,5,LEN(@FullName)))+4,'') Lastname1
Not right as it gives
Vauxhall
Adam Rocks AIR Vauxhall
Ideally the result should be
Vauxhall
Adam Rocks AIR
View 6 Replies
View Related
Jun 30, 2015
I have two linked tables from two different databases, there is a column "product" on each table however the product on one table has a Prefix so not a direct match. How can I join these tables ? In the query I have used product2: Replace([scheme_pos.product],"-B","") then tried Joining on product2 but it says JOIN not supported.
View 2 Replies
View Related
Jul 6, 2015
I have a column that has data in column user4 such as :
CALBRIG ALBION COMPANY LTD
1900 No. 8 Road, RIchmond, BC, V6V 1W3
I can do a LEFT(user4, 7) That returns me the first 7 charcters but I need to retrieve the company information but the number of characters may vary with each field. Is there anyway I can do this without returning the entire line?
View 3 Replies
View Related
Jul 24, 2015
As I understood, if SPARSE is used on a column, which have many NULL marks, then the storage could be efficently used (we need less spaces to save NULL marks, hence a table which has many NULL marks with SPARSE property needs less storage than the same table, but without SPARSE. I created two table as follow:
/******* Table with Sparse ******/CREATE TABLE Sprstb(
unsprsid INT IDENTITY(1,1) NOT NULL,
Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
Tel int NOT NULL,
adress nvarchar(60) SPARSE NULL)/***** Table without Sparse*******/CREATE TABLE Unsprstb(unsprsid INT IDENTITY(1,1) NOT NULL,Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
Tel int NOT NULL,
address nvarchar(60) NULL)
I have populated the Sprstb with 5 Milion records. It needs 509,961 MB storage. Then I have copied this table into Unsprstb
SET IDENTITY_INSERT [dbo].[Unsprstb] ON
Insert [dbo].[Unsprstb](unsprsid,Firstname,Lastname,Tel,adress)
SELECT unsprsid,Firstname,Lastname,Tel, adress FROM [dbo].[Sprstb]
SET IDENTITY_INSERT [dbo].[Unsprstb] OFF
The Unssprstb need only 466,031MB !
That means the Table with SPARSE column need more storage, Why?
By the way, in table Sprstb column address has 1666198 Null mark (from 5000000)
View 5 Replies
View Related
Jul 28, 2015
I have a requirement where i want to delete the records based on the Date column. I have table which contain the columns like machinename ,lasthardwarescandate
I want to delete the records based on the max(Lasthardwarescandate) i.e. latest one, column where the machine name is duplicate menace it repeats. So how would i remove the duplicate machine names based on the Lasthardwarescandate column(There are multiple entries for the Lasthardwarescandate so i want to fetch the latest date column).
Note: Duplication should be removed based on “Last Hardware Scan” date.
Only latest date should be considered from multiple records for the same system. "
View 4 Replies
View Related
Oct 27, 2015
I want to alter my all tables to change the name of all columns.
I need this because all column names were created with space and I want to remove the space for future work .
For example – In Table Customer there is a column name [Cust_Id ] and I want to change it with [Cust_Id]
View 11 Replies
View Related
