February 20, 2017 at 12:52 pm
I have a table that has Machinename that looks like the following:
TH1239.CDB.LOCAL
TH1238.CDB.LOCAL
TH1237.CDB.LOCAL
TH1236.CDB.LOCAL
TSB1-TLA.CDB.LOCAL
TSB2-TLA.CDB.LOCAL
I would like to remove everything after the .
SO it looks like
TH1239
TH1238
TH1237
TH1236
TSB-TLA1
TSB-TLA1
This didn't work for me
SELECT MachineName
,LEFT(MachineName, CHARINDEX('.', MachineName) - 1) AS [MCNAME]
from dbo.FAILED
gave me this error---- Invalid length parameter passed to the LEFT or SUBSTRING function.
February 20, 2017 at 1:24 pm
RellB - Monday, February 20, 2017 12:52 PMI have a table that has Machinename that looks like the following:TH1239.CDB.LOCAL
TH1238.CDB.LOCAL
TH1237.CDB.LOCAL
TH1236.CDB.LOCAL
TSB1-TLA.CDB.LOCAL
TSB2-TLA.CDB.LOCALI would like to remove everything after the .
SO it looks like
TH1239
TH1238
TH1237
TH1236
TSB-TLA1
TSB-TLA1This didn't work for me
SELECT MachineName
,LEFT(MachineName, CHARINDEX('.', MachineName) - 1) AS [MCNAME]
from dbo.FAILEDgave me this error---- Invalid length parameter passed to the LEFT or SUBSTRING function.
Your data looks different than what's posted to have this error. You can get the error if you have values with trailing spaces, values without a period and probably other things. So you would want to look at your data as that's causing the error. Not sure what data is allowed, what the data type is or what constraints are in place.
Sue
February 20, 2017 at 1:24 pm
Those specific examples you gave will work fine for that query, however, the problem is that somewhere in your data, you have something that doesn't work.
You could filter those results out in your where clause (see below), however, I would suggest maybe selecting those results out first, and seeing if they need addressing themselves. This will return your bad rows:WHERE CHARINDEX('.', MachineName) = 0
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 20, 2017 at 1:33 pm
RellB - Monday, February 20, 2017 12:52 PMI have a table that has Machinename that looks like the following:TH1239.CDB.LOCAL
TH1238.CDB.LOCAL
TH1237.CDB.LOCAL
TH1236.CDB.LOCAL
TSB1-TLA.CDB.LOCAL
TSB2-TLA.CDB.LOCALI would like to remove everything after the .
SO it looks like
TH1239
TH1238
TH1237
TH1236
TSB-TLA1
TSB-TLA1This didn't work for me
SELECT MachineName
,LEFT(MachineName, CHARINDEX('.', MachineName) - 1) AS [MCNAME]
from dbo.FAILEDgave me this error---- Invalid length parameter passed to the LEFT or SUBSTRING function.
Check for the 0 returned by CHARINDEX when the search character(s) are not found. Convert the 0 to NULL with NULLIF, then to 8000 with ISNULL.WITH SomeSampleData AS (
SELECT *
FROM (VALUES
('TH1239.CDB.LOCAL'),
('TH1238.CDB.LOCAL'),
('TH1237.CDB.LOCAL'),
('TH1236.CDB.LOCAL'),
('TSB1-TLA.CDB.LOCAL'),
('TSB2-TLA.CDB.LOCAL'),
('TSB2-TLA')
) d (MachineName)
)
SELECT *, LEFT(MachineName,ISNULL(NULLIF(CHARINDEX('.', MachineName),0),8000)-1)
FROM SomeSampleData
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 20, 2017 at 2:00 pm
My preferred option is to add the character at the end of the string so it won't ever be 0.DECLARE @Failed TABLE(
MachineNAme varchar(100))
INSERT INTO @Failed
VALUES
('TH1239.CDB.LOCAL '),
('TH1238.CDB.LOCAL '),
('TH1237.CDB.LOCAL '),
('TH1236.CDB.LOCAL '),
('TSB1-TLA.CDB.LOCAL'),
('TSB2-TLA.CDB.LOCAL'),
('TSB-TLA1');
SELECT MachineName
,LEFT(MachineName, CHARINDEX('.', MachineName + '.') - 1) AS [MCNAME]
from @FAILED
February 20, 2017 at 2:01 pm
use a case statement and don't parse it when the period does not exist.
SELECT MachineName,
CASE
WHEN CHARINDEX('.', MachineName) > 0
THEN LEFT(MachineName, CHARINDEX('.', MachineName) - 1)
ELSE MachineName
END AS [MCNAME]
from dbo.FAILED
Lowell
February 20, 2017 at 2:49 pm
Worked like a Charm. Thank you!
February 21, 2017 at 1:11 pm
Just to toss it out there, you could potentially also use PARSENAME here:SELECT PARSENAME(MachineNAme, 3)
FROM dbo.Failed
WHERE PARSENAME(MachineNAme, 3) IS NOT NULL
February 21, 2017 at 1:36 pm
doug.brown - Tuesday, February 21, 2017 1:11 PMJust to toss it out there, you could potentially also use PARSENAME here:SELECT PARSENAME(MachineNAme, 3)
FROM dbo.Failed
WHERE PARSENAME(MachineNAme, 3) IS NOT NULL
That query misses rows, that might not be what the OP expects.
February 21, 2017 at 2:02 pm
Luis Cazares - Tuesday, February 21, 2017 1:36 PMdoug.brown - Tuesday, February 21, 2017 1:11 PMJust to toss it out there, you could potentially also use PARSENAME here:SELECT PARSENAME(MachineNAme, 3)
FROM dbo.Failed
WHERE PARSENAME(MachineNAme, 3) IS NOT NULLThat query misses rows, that might not be what the OP expects.
The query only misses rows if the OP is looking for all machine names to be returned, whether there is a period in the name or not. That was not stated in the original requirement.
But if you do want all the names, a simple modification gets them: SELECT ISNULL(PARSENAME(MachineNAme, 3), MachineNAme)
FROM dbo.Failed
I was just pointing out that PARSENAME is yet another way of extracting part of a string before a particular delimiter, especially if the delimiter is a period.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply