Hi
I have a field that starts with numbers and then has a description after if, eg.
"123 This is the description for string 123"
1. How would I go about stripping out the first instance of number to leave the string as "This is the description for string 123"
thanks.
Bill
I am trying to Strip off the Numbers witha Delimited List and just retain the Name of the Persons. but unable to do it. is there any function or code to do that in SQL
932908` James Fleser,935992` Prakash Sinha
Stripping off Numbers for the Above and Just retain the Names..
thanks
Hello, I have this Access 2K query that I need to re-create in MS SQLServer 2000, so I'm using the Query Analyzer to test it.One of the Access fields stores the home phone number. In the Accessquery, if the phone number is null, it fills it up with zeroes"000000000." If the phone has an input mask, it only gets the 9 numbers(area code included) and if the phone number's good (all numbers) thenit leaves it alone. That Access query is using immediate ifs toaccomplish that task.Does anyone have any idea how to copy this behavior into SQL Server2000? I've using the CASE statement but so far my code is not correct.I get stuck in the input mask. This is the Access code:HomePhone:IIf(IsNull([HomePhone]),"0000000000",IIf(Left([HomePhone],1)="(",Right(Left([Homephone],4),3)& Right(Left([Homephone],9),3) & Right([HomePhone],4),[HomePhone]))Thanks for all your help.JR.
View 2 Replies View RelatedHi,
As part of a data search project I need to be able to strip all non numeric characters from a text field. The field contains various forms of phone number in various formats. In order to search on it I am going to remove all non numeric characters from the input criteria and from the data being searched.
In order to do this I decided on using a SQL Server custom function: Pass in field. Loop through all chars, test against asci values for number range. return only numernic data concatenated into a string.
Are there any other more efficient ways of going about this?
Hi there - I would like to share this strip of code with our SQL 2000DBA community. The code below strips all non-numeric characters from agiven string field and rebuilds the string. Very simple, but I had tobuild it from scratch due the lack of info on this specific matter. Iam sure there are better solutions out there, although I will be gladif this script can help anyone. Feel free to modify and comment itback.Regards,Rubem Linn JuniorMCSE, .NET developerWeb Apps Specialist------------------------------------------------------- BEGIN---------------------------------------------------DECLARE @String_Length AS INTEGER -- Length of the given stringDECLARE @Original_String as NVARCHAR(50) -- The field to stripnon-numeric charsDECLARE @counter as integer -- simple counter variableDECLARE @Stripped_String as nvarchar(50) -- The field after beenstripped-- Get the length of the field (string) to be parsedSELECT @String_Length = len(someStringField) FROM SomeTable WHEREFilterID = 001-- Get the field (string) to be parsedSELECT @Original_String = someStringField FROM SomeTable WHEREFilterID = 001-- Set counter variable to 1SELECT @counter = 1-- Reset this variableSELECT @Stripped_String = ''-- Initiate loop from 1 to the Length of the given stringWHILE (@counter) <= @String_LengthBEGIN-- Check if the char in the lap is numericif substring(@Original_String,@counter,1) LIKE '[0-9]'BEGIN-- Load this variable with the non-numeric-- data stripped from the original stringselect @Stripped_String = @Stripped_String +substring(@Original_String,@counter,1)END-- Increment the counter by oneselect @counter = @counter + 1END-- Print the original string with all charactersPRINT @Original_String-- Print the numeric data that was stripped outPRINT RTRIM(LTRIM(@Stripped_String))
View 1 Replies View RelatedA table I'm working with has a varchar column containing a comma-delimited string of numbers, which match up to smallint codes in another table. I gather this is someone's implementation of a many-to-many relationship. :)
Using SQL Server 2008 and wondering if there's a special trick to using a string list of numbers with IN() URL...converting the string into a temp table[/url] but I'd just like to make sure there isn't a quicker, easier approach, or if that's it.
Hi,
Say I have a string of Numbers eg. "1,2,3,4,5" and in order to split it I use split("1,2,3,4,5", ",") function, what would I get as an answer? My second question is, if I want to pass all 5 numbers in a sub report as if I am selecting from a multi-value parameter, how would I do that? I have tried several different ways of doing it and I still can't get it to work. Please help me...and feel free to email me back if somethign is not clear.
Thanks a lot,
-Rohit
I basic question but can someone help.
I have a SELECT statement, the result of which populates a
datagrid. The first column has consecutive dates in it and I want
to hyperlink each date to a seperate Javascript function (the
Javascript is created on the fly and is unique for each date). I
need a different function name for each function and so tried the date
but "/" is not allowed in the Javasript function name. I think
the easiest way will be to produce a new column with the date expresses
ddmmyyyy, ddmmyy or some such unique number (but not dd/mm/yyyy).
I tried :-
"CASE " & _
"WHEN t3.date = t3.date THEN (DAY(t3.Date) + MONTH(t3.Date) + YEAR(t3.Date)) ELSE NULL END AS [javaKey]
but this adds the year to the month to the day - not a unique result as 1/2/06 and 2/1/06 are the same.
I am just getting to grips with VB.Net (as an amature) but am a distinct beginner at SQL!
Many thanks
Mike
I have a column in a table which looks like below.
Column
-------
AA123
D123
AXC1
QF23
I need to split this value into two part, Alphabets and numeric. How to do this using SQL query.My column value will not have mixed characters like A1D3,G32S,12F.It will always follow the ablve pattern mentioned above.
I've got a string of numbers, say 123456 (the actually number is 12 digitslong). I need to calculate the sum of each individual number in the stringof numbers, so in the example of 123456 the sum would be 21 (1+2+3+4+5+6).Thanks
View 5 Replies View RelatedI have a string and i want to get only the numbers from right.
For example if I have the string Like '123756zxfggr123456' then it will show me only 123456 or if i have the string like
'4vbz67xfggr123dfd' then it will show me only 123 or if i have the string like '123756zxfgg43r5' then it will show me only 5.
I got a function where it gives me all the numbers in a string but I don't need that
CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
[Code] ....
If I ran the select statement it gives me the result 111123456 but i want only 123456 or if i select
SELECT dbo.udf_GetNumeric('111zxfggr6587fhhfkwee') AS 'Num' it will show me 6587.
Anyny in-built sql function that gives us numeric values in a string?
I have to deal with some inconsistent US phone numbers stored in DB. They are stored as
(xxx)xxx-xxxx
xxx xxxx xxxx
(xxx) xxx-xxxx
xxx-xxx-xxxx
xxxxxxxxxx
I don't want to apply nested REPLACE function to eliminate all unnecessary characters to get 10 digit number from DB.
Yeah, it's pretty simple. Maybe it'll help someone out.
-- USAGE: fn_extract_chars(string_to_search, 'letters' -or- 'numbers')
CREATE FUNCTION fn_extract_chars (@x varchar(128), @y char(7))
RETURNS varchar(128)
AS
BEGIN
DECLARE @chars varchar(128)
DECLARE @pos int
DECLARE @action varchar(32)
SET @pos = 0
SET @chars = ''
IF @y = 'numbers' SET @action = '[0-9]'
ELSE IF @y = 'letters' SET @action = '[a-zA-Z]'
WHILE @pos < (DATALENGTH(@x) + 1)
BEGIN
IF PATINDEX(@action,SUBSTRING(@x, @pos, 1)) > 0
BEGIN
SET @chars = @chars + (SELECT SUBSTRING(@x, @pos, 1))
END
SET @pos = @pos + 1
END
RETURN(@chars)
END
Hello,
I'm having problems with a column of numeric string data coming from a tab delimited CSV file. When a number in the column is negative, it is expressed this way; 1,240.52-
The negative symbol occurs at the end of the numeric string. The destination column is in a SQL Server 2005 table. I've tried to change the data type of the table column, and three or four different data types in a data conversion task, but nothing has worked so far to bring the data over.
Any ideas?
Thank you for your help!
cdun2
I'm trying to put conditional formatting on a field, that behaves as follows:
The data in the field is varchar, and sample data is either:
NULL
3.0 :0
11.7 :1 (these are ratios of a sort)
I want to evaluate the first 3 characters of the string as numbers.
Example:
Mid(fieldvalue,1,3) = "3.0" or "11."
Any data that is greater than 1.99, I want to make the background dark red, anything else including nulls, zebra formatting. I have the following expression built so far and it appears to work, except when the value is null. If the value is null, it leaves the background color white.
This is the warning: [rsRuntimeErrorInExpression] The BackgroundColor expression for the text box "Asthma" contains an error: Input string was not in a correct format.
=iif(
isnothing(Fields!Asthma.Value)
,(IIf(RowNumber(Nothing) Mod 2 = 0,"#b8cce4","#dbe5f1"))
,(iif(mid(Fields!Asthma.Value,1,3)>1.99
,"DarkRed"
,IIf(RowNumber(Nothing) Mod 2 = 0,"#b8cce4","#dbe5f1"))))
My logic is, if the field is null, zebra format, if mid of the value is > 1.99, dark red, everything else zebra formatting. As I said, this seems to work except for nulls.
Hello,
I am trying to extract from some strings like the following strings the number and order them by that number:
Box 1
Box 2
Box 3
Box 20
Box 21
...(and so on)
The problem I am having is that I already extracted the number using
Substring([field],[starting position],[lenght])
but the output seems to be in a string format, so the order is not in an ascending order.
Thanks for any suggestions.
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?
I want to strip off the last three characters from an item number. The only thing is tht every item number is not the same lenght. The last three characters of this number are packing codes that I do not need. Fore example I can have all these numbers:
EFJ50701033
EFW1546066
RFM4925156
70561033
89541899
How would I remove the last three characters and only remain with whatever is in front of those three characters?
Any help is greatly appreciated!
Does anyone know how to get rid of rtf tags that are stored in the table? I need to filter out the data and wondering if there is a utility on the SQL Server that can do it.
View 16 Replies View RelatedHas anyone had to deal with removing punctuation (commas, apostrophes,etc.) from a column? What is the most efficient way to have thesecharacters automatically removed from the column?
View 3 Replies View RelatedWhy does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?
View 7 Replies View RelatedHi everyone,
I have a sql quey that selectes phoneNumbers from the database. Problem is some phone numbers have hypens in it and some doesn't. Is there any way in sql so that I can remove hyphens from the phone numbers
some numbers are like this
213-456-9999
and some are
2136789999
Please let me know if this is possible.
Thanks.
Question - if you had to completely strip all permissions from all databases in an instance and reset them, assuming you have metadata to support rebuilding the permissions, what steps would you follow? I can handle the iterating through each database, but at the database level, what steps would you take?
The reason I inquire on this is I currently have a job that I inherited that does just this. But it's buggy and was also written in for SQL Server 2000. With some of the changes in 2005, a few bugs have crept in, etc. And I would like to confirm my thoughts this. Or, if your opinion is why are you wasting your time on this? Then that's fine to and I'll review any constructive comments you may have.
Hi All,
I'm having trouble coming up with a function that will allow me to display only the date and time from a string in the following format:
JSMITH 1/1/2006 1:00:00AM
I've tried using substr with a negative position value, but since the date and time for each instance can be different, with it being anywhere from 18-21 characters, if the length is anything less than 20-21 characters, it will return part of the end of the username. What function can I use to only retrieve the date and time?
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
Thanks for any help
I'm trying to strip out the First & Last Name from an Email Address. The email address is formatted as "FirstName.LastName@emailaddress.com" but sometimes a middle initial is used (ex: "First.M.Last@emailaddress.com").
I can locate the '@' sign and the first '.' period(from left to right) but need to find the first '.' from the left of the '@' sign.
I'm doing this in a view.
Here's some sample data and what I've tried:
CREATE TABLE dbo.Table1
(EmailAddr VARCHAR(255))
INSERT INTO dbo.Table1 (EmailAddr)
SELECT 'Andy.Smith@gmaddr.com'
UNION ALL
SELECT 'Betty.Lee-Jones@gmaddr.com'
[Code] .....
I have a table that contains an nvarchar column of data. The data is actually a monetary value; sometimes with a decimal point sometimes without.
My problem is that I need to strip the decimal portion of the string if it exists. From a select statement I can use:
SUBSTRING(DW_OBP_ORD_TMP_IC.VALUE,1,LEN(VALUE)-(LEN(VALUE)-CHARINDEX('.',DW_OBP_ORD_TMP_IC.VALUE)
if a decimal point exists. But if one does not the CharIndex comes back 0 and my equation does not work correctly.
I need to pull the data from the table, along with many other fields. How do I do this without using a cursor? I have millions of rows so need a solution that will be quick.
thanks in advance,
Marilyn
I have a filename as follows:
123_20151016_3152_AIRHtest1.txt
What I would like to extract from this fill name is the "3152" only. What is the correct way to do this?
I am exporting from a SQL money field to a flat file. The datatype is set to DT_CY in SSIS. I need two decimal places in the flat file but instead I am getting 1.1 instead of 1.10.
Should I be using a different datatype or is there a way to set the number of decimals on the DT_CY datatype?
I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:
select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)
but don't know how. Any clues?
I'm trying to write data to excel from an ssis component to a excel destination.
Even thought I'm writing numerics, every cell gets this error with a green tag:
Convert numbers stored as text to numbers
Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.
I'm hearing this a common problem -
On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:
<style>.text { mso-number-format:@; } </style>
is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.
Maybe some setting in my write drivers - using sql servers excel destination.
So close.. Thanks for any help or information.
I have a scenario where in I need to use a comma delimited string as input. And search the tables with each and every string in the comma delimited string.
Example:
DECLARE @StrInput NVARCHAR(2000) = '.NET,Java, Python'
SELECT * FROM TABLE WHERE titleName = '.NET' AND titleName='java' AND titleName = 'Python'
As shown in the example above I need to take the comma delimited string as input and search each individual string like in the select statement.
We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.
View 9 Replies View Related