June 3, 2013 at 12:04 pm
Hello Everyone
I hope that you are having a very nice monday.
I have found a column that is storing incorrect data. How about that :))
This is a sample of the data
1596424@DAR
I want only the numerals, nothing at all past the at symbol.
This is all that I need
1596424
The length of the numeric value is not always the same length.
How may I get only the numeric value from the string? I am drawing a total blank on this one.
Thank you for your advice, assistance and comments
Andrew SQLDBA
June 3, 2013 at 12:16 pm
If the Data ALWAYS contains the @ symbol, you could use some of the SubString functions:
;With MySampleData([val])
AS
(
SELECT '1596424@DAR'
)
SELECT *,
LEFT(val,CHARINDEX('@',val) -1) as OneWay,
SUBSTRING(val,1,CHARINDEX('@',val) -1) As AnotherWay,
SUBSTRING(val,CHARINDEX('@',val) +1,30) As RightSideWay
FROM MySampleData
WHERE CHARINDEX('@',val) > 0
Lowell
June 3, 2013 at 12:33 pm
Thank You Lowell
That works perfect. I appreciate the other code to the right wide of the at symbol.
Andrew SQLDBA
June 3, 2013 at 12:41 pm
AndrewSQLDBA (6/3/2013)
Thank You LowellThat works perfect. I appreciate the other code to the right wide of the at symbol.
Andrew SQLDBA
glad i could help; if there were more than just two things to chop out of the original string, i'd start suggesting something else, like the DelimitedSplit8k, or something like PARSENAME, but substrings are quick and easy for small jobs.
Lowell
June 3, 2013 at 12:57 pm
I have used Substring, but I could not get it to work if there were a different number of characters. CharIndex takes care of that. And that is the part I had totally gone blank on.
Thanks again
Andrew SQLDBA
June 4, 2013 at 8:42 pm
What if the first non-numeric character in the string is not @?
Answer: You could use PATINDEX instead of CHARINDEX.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 4, 2013 at 10:50 pm
Try the below function, Whateveru want u can do with this
CREATE FUNCTION GEN_FN_StripCharacters
(
@strInputString NVARCHAR(MAX),
@strMatchExpression VARCHAR(255)
)
/*
--- Purpose : To remove the specified Characters in the Given String
Alphabetic only: SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z')
Numeric only: SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^0-9+-/')
Alphanumeric only: SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z0-9')
Non-alphanumeric: SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', 'a-z0-9')
*/
RETURNS NVARCHAR(MAX)
AS
BEGIN
SET @strMatchExpression = '%['+@strMatchExpression+']%'
WHILE PatIndex(@strMatchExpression, @strInputString) > 0
SET @strInputString = Stuff(@strInputString, PatIndex(@strMatchExpression, @strInputString), 1, '')
RETURN @strInputString
END
June 5, 2013 at 1:48 am
I was thinking more something like this:
With MySampleData([val])
AS
(
SELECT '1596424@DAR'
)
SELECT *,
LEFT(val,PATINDEX('%[^0-9]%', val)-1) as OneWay
FROM MySampleData;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 5, 2013 at 10:28 am
You can use REPLACE() function to strip character sequences from a varchar column like so:
select replace( col1, '@DAR', '' );
You can also use the LIKE operator to return (or check) columns values that contain a character that doesn't fall within a defined alpha-numeric range. In the example below, I'm returning all occurrances of col1 which contain a chancter not within 0 - 9, which is what you're looking for.
select col1 from table1 where col1 like '%[^0-9]%';
To prevent invalid data from being inserted into a column going forward, you can add a check constraint to the table that leverages the same above LIKE expression. For example:
alter table table1
with check
add constraint cc_col1 check (col1 not like '%[^0-9]%');
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply