Hi all
Which function should I use to remove alphabet in a string?
For example, 60a , 50b, 34s, 34k. I want to remove the suffix alphabet. I tried to use filter but it return an array. i want the return value to be string or int to display.
Thanks
Bryan
Is there an easy way to loop through all rows and remove allinternational alphabet characters from a column in a table, for exampleremove German umlauts "ü" and convert them to a simple "u".Thanks,lq
View 12 Replies View RelatedHi,Does anyone have a function which replaces accent chars from a stringwith the non-accented equivalent? For example 'hôpital' should return'hopital'.Thank you in advance.
View 3 Replies View RelatedI need to increment values
I have written this script execute this :
This is for this scenario
Insert Into #String SELECT 'S601-S630',1,'Desc1'
Drop table #IDs
CREATE TABLE #IDs(ID INT IDENTITY(1,1) ,String VARCHAR(100),Flag Int,Description VARCHAR(1000))
CREATE TABLE #string(ID INT IDENTITY(1,1) ,String VARCHAR(100),Falg Int,Descript VARCHAR(1000))
DECLARE @min INT ,@Max INT ,@String VARCHAR(50),@Start VARCHAR(50),@End VARCHAR(50),@Flag INT,@Desc VARCHAR(1000)
[Code] ....
How I need to increment if the input is like this
Insert Into #String SELECT '6S01-6S30',1,'Desc1'
if alphabet is in middle of the number??
The issue being we have a category field that has Business;Client;Stuff. I would like to remove just the Business and leave the other stuff. Ive tried the link below and it removes everything..
update dbo.TBL_CONTACT
set CATEGORY = REPLACE(CATEGORY, 'Business', NULL)
TableA
Col1
----
13.4
13.4 a
13..4
13,.4
Result for Table A needed:
Col1 ColCalculated
--------------------------
13.4 13.4
13.4 a null
13..4 null
13,.4 null
how can I achieve it.
does anyone know where i can find a user defined function that replaces accented characters with their normal ones
i want to replace all accented characters since for some reason sql server does not store the characters properly, or when they exported to excel for html they are displayed incorrectly
thanks
Hi,
I am looking for a function that can remove the brackets and text within them in a given string.
i.e. 'Hello World (text in brackets)' becomes just 'Hello World'
Thanks alot
Hi,I am creating creating a table with a Date column dd-mm-yyyy. But Icant seem to find a SQL function that just returns today's date.getDate() returns the time as well so I cant use it.The reason is simply that I want to update/overwrite over and overagain all records from current day but not touch the ones fromyesterday etc and with the timestamp in there I just end up addingmore and more rows for the same day.In other words I only want to preserve rows are from yesterday orolder but overwrite ones from today.Any help will be appricated.Thank you!Yas
View 3 Replies View RelatedHi,
In a extract that i have the SS# is as 123-45-6789 how do i remove the dashes in the string and have the final expression of 123456789.
thanks
Karen
There are unwanted characters(''','/','&'.. etc) in column.
I need to remove these characters
Hi All,
I have one field with different values like
F1
A,B,C,
D,E
G,H,I,
My requirement is to remove the last comma from that string. I need the output should look like
F1
A,B,C
D,E
G,H,I
thanks,
Mears
I have a phone number field.
I would like to remove the hypens and brackets.
Do anyone know of any functions, I can use to accomplish this??
Thanks in advance!
--Vic
I have a Table-valued Function already defined in my DB. When I look at it's Properties, it lists Encrypted TRUE.
How can I set Encrypted to FALSE? Is there a t-sql command to ALTER the Function to set Encrypted False?
(fyi, I am syadmin and local admin on this SQL 2008 R2 box)
street_name
---------------------
1A HAYES ST
11a yONU STREET
i need to parse off watever is in front of the street_name. anyone
has any approach>?
street_name
-------------
HAYES ST
yonu street
I have a column of string which has html tags attached to it. How can I remove them..other than manually going and doing it? Any funtions?
Thanks!!
Tanya
I am looking for a function or way to return only results which does not include appended characters to order numbers.
For instance, below is a list of order numbers. I only want the order number that is SO-123456
OrderNumbers
SO-123456
SO-123456-01
SO-123456-2
SO-123457
SO-123457-1
SO-123457-02
SO-123458
I would like my query to only show the below results
SO-123456
SO-123457
SO-123458
What functions or query methods could achieve this?
I was hoping for something similar to RTRIM but that is only specific to white space.
My sample data is as below.
plan type A change from plan type B from Plan type C
Insurance plan M changed from Insurance plan b From plan d from Plan N
Now from above strings i want to remove all data from second appearance of from. i.e. i want display values as below.
plan type A change from plan type B
Insurance plan M changed from Insurance plan b
I have a field that contains some text. Each field will have none or atleast one comment in it. A comment can be a string of any length with *** on both sides. Ex: ***comment***
Declare @Test_tbl Table(TextField Text)
Insert Into @Test_tbl
Select 'Some text ***comment*** some more text' Union all
Select 'Other text ***another comment*** more and more text' Union all
Select '***Comment*** some text ***More Comments***' Union all
Select 'some text with no comment'
I need the output be ...
TextField
--------------
Some text some more text
Other text more and more text
some text
some text with no comment
Thanks in advance.
Looking at a trace table and trying to remove all the "erroneous" bits in a string e.g the declares etc so I can purely get to proc names.
An example string
declare @p2 varchar(10) set @p2=NULL exec sp_proc @Code='TF',@TypeCode=@p2 output select @p2
I've tried
select top 5000 textdata,substring(textdata,charindex('exec',textdata)+5,charindex('@',textdata)-1)
from trace_table
where TextData like '%sp_%'
and TextData like '%declare%'
And it fails dismally...
I want to remove special characters from a string in sql like <?> in a column value in a table.
View 1 Replies View RelatedHow can i perform this task with ssis  OR TRANSACT SQL? I HAVE THESE ROWS WITH THE NEXT DATA, I want to take just the valid one, BUT I HAVE A LOT OF COMBINATIONS AS following names, it can be animals, things or personal names
GABRIEL OBANDO --CORRECT
GABRIEL OVANDO
Gavriel OVANDO
gAbriel OBANDO
GABRIE OBANDO
Gabri OBONDA
MANAGUA --CORRECT
NANAGUA
NAMAGUA
Hi to all,
I am having a string like (234) 522-4342.
i have to remove the non numeric characters from the above string.
Please help me in this regards.
Thanks in advance.
M.ArulMani
Hi to all,
I am having a string like (234) 522-4342.
i have to remove the non numeric characters from the above string.
Please help me in this regards.
Thanks in advance.
M.ArulMani
I have a SQL select syntax as below
0 AS SalaryMin,
2088 AS SalaryMax,
2088 AS BillableHours,
'Month' AS SalaryPaidCode,
0 AS SalaryBreakdownHourly,
0 AS SalaryBreakdownDaily,
[Code] ...
While outputting to CSV.file
I got :0,2088,2088,"Month",0,0,0,0,0,0,0,"N/A","N/A","G","N/A","Exempt","Other",1
How can I remove all double quotes in the string fields? so that O can get the result as below while the output
0,2088,2088,Month,0,0,0,0,0,0,0,N/A,N/A,G,N/A,Exempt,Other,1
I 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?
I 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'
I have one table with one column:
region
Global
USA
England
Germany
Spain
What I want is that the first entry Global is always in the first row the rest should be ordered by alphabet.
I tried this but order by works only for the result of union:
SELECT * FROM csappcidb_region WHERE region='Global'
UNION ALL
SELECT * FROM csappcidb_region WHERE region<>'Global' ORDER BY region
What else can I do?
Got a tough one here for you SQL junkies.I'm working on a website (in ASP) for a national greek/collegeorganization. All it's college chapters have greek chapter names,i.e. Alpha Chapter, Delta Chapter, Delta Iota Chapter, Gamma PhiChapter, ect. ect.I need the SQL to return the chapters in greek alphabet ordering. Seebelow:1. Alpha2. Beta3. Gamma4. Delta5. Epsilon6. Zeta7. Eta8. Theta9. Iota10. Kappa11. Lambda12. Mu13. Nu14. Xi15. Omicron16. Pi17. Rho18. Sigma19. Tau20. Upsilon21. Phi22. Chi23. Psi24. OmegaBasically I need a way to specify this type of presedence for wordingand keep all the results in greek-letter order, so Sigma Upsilon comesbefore Sigma Tau, and so on.Also, to make it even more difficult, these are written words, not theactual greek symbols like α β etc. Everything is stored inthe DB as Alpha Beta Whateva Chapter.Hopefully some of you SQL junkies will be able to help on this one ;)
View 2 Replies View RelatedI'm presented with a problem where I have a database table which must be migrated via a "custom tool", moving the data into a new table which has special character requirements that didn't exist in the source database. My data resides in an SQL Server 2008R2 instance.
I envision a one-time query which will loop through selected records and replace the offending characters with --, however I'm having trouble understanding how this works.
There are roughly 2500 records which meet the criteria of "contains bad characters", frequently containing multiple separate bad chars, and the table contains roughly 100000 rows.
Special Characters are defined as #%&*:<>?/{}|~ and ..
While the field is called "Filename" it isn't always so, it is a parent/child table where foldernames are also stored.
Example data:
Tablename = Items
ItemID Filename ListID
1 Badfile<2015>.docx 15
2 Goodfile.docx 15
3 MoreBad#.docx 15
4 Dog&Cat#17.pdf 15
5 John's "Special" Folder 16
The examples I'm finding are all oriented around SELECT statements, to change the output of what I see returned, however I'd rather just fix the entire column using an UPDATE. Initial testing using REPLACE fails because I don't always have a single character as the bad thing in a string.
In a better solution, I found an example using a User Defined Function to modify the output of a select, but I cannot use that UDF in an UPDATE.
My alternative is to learn enough C# to modify the "migration tool" to do this in-transit, but I know even less about C# than I do of SQL.
I gather I want to use @@ROWCOUNT to loop through the rows but I really can't put it all together in a cohesive way.
I have a basic while loop but I need to be able to increment the counter from one alpha character to the next:
declare @counter nvarchar(10)
set @counter = 'A'
while @counter < 'Z'
beginprint 'the counter is ' + @counter
set @counter = @counter + @counter
end
In this example I would need to see the following output:
the counter is Athe counter is Bthe counter is Cthe counter is D.....
Is this possible in SQL?
I need to create constraint in column to add only alphabet .
like "adc" ,"sdfsd" and not "1234adfd".plz reply soon.
There are about 300 fields in a views.
How to reorder all fields by alphabet?
For example, from script of views like below:
select name, date, amount from order
re-order fields' name like below:
select amount, date, name from order
For about 300 fields view, how to code to re-order fields' name?