Capitalize A Text String/String Function Related
Hi Folks:
How can I capitalize a string like 'JOHN DOE' into
'John Doe' with one SQL statement. SQL does provide
string function like LOWER(char_expr) which would
change the whole text string to 'john doe' and I don't
want that.
So far, in order to do that, I have to use a front-end
development language like 'Omnis' which has a
string function 'cap()' to capitalize the whole string,
then update the back-end SQL with the new string.
Thank you in advance for your time and advice.
David Nguyen
View Complete Forum Thread with Replies
Related Forum Messages:
String Function
select distinct(f.agency+y.year+s.type) as SDNFIn this select clause, I try to use the last 2 digit of year for y.yearvalue. which function should i apply to it?
View Replies !
Right And String Function
I need to take the character from a text field. I need the character which is the second one from the end(right). Like out of '12345' or '99821' I would need the 4 from the first and the 2 from the second. Can I combine a string and a right statement to do this? Thanks
View Replies !
A String Function
does anyone know a function to count the number of times a substring appears in a string? example NumberOfTimes("i","mississippi") would return 4
View Replies !
STRING FUNCTION
Hi! Is there any fucntion avaible in SQL for search a character position in a string. e.g. 'ABC-DEF' - is located at the position of 4 I tried the charIndex, Is it same as Instr in ORACLE. Is there any INSTR function available
View Replies !
String Function
Hi all, iam trying to find a string function which would replace column value where there is a ssn with 1 and anything else(blank,null,...) with 0. i need to count the number of rows with ssn and one without ssn. i checked few string functions but no use any help appreciated
View Replies !
SQL String Function Help
Here is the scenario. I have a 10 position varchar field that holds a project number. The project number is in the form yy-nnn... where yy is the current year (06) and nnn is a sequential number. The project number is displayed on a data entry form so that the next record has the next sequential number. So when the data entry form first opens the project number field has 06-1 after that record is entered the form is cleared and reinitialized and the project number now shows 06-2. If the form is closed and then reopened the project number would still show 06-2 if that previous record was not entered and 06-3 if it was. The way I am going about getting the next sequential number is the following: SELECT MAX(RIGHT(ERFNumber, LEN(ERFNumber)-3)) AS MaxERFNumber From Table1 This approach is working fantastic until record 06-9 is entered. The above line does determine that 06-9 is the MAX record and then creates a record 06-10 but every subsequent time a record is attempted it still thinks 06-9 is the MAX value of the above statement. I am assuming that since the field is defined as a varchar the MAX function is applying character logic versus numeric logic thus the 9 being treated as the MAX value. Is there an SQL function that can change the result of RIGHT(ERFNumber, LEN(ERFNumber)-3) to a numeric value so that the MAX function will work correctly. I know there are many other ways to do this but in the interest of time because the application is almost finished, changing this now is going to be a major undertaking. Any help or guidence would be greatly appreciated.
View Replies !
SUM Function For String????
hi kristen or anybody there, is there a way you can concat string columns in a select statement... i have two tables (joined) and i wanted to display a concatenated values. data details: table 1 pk_transactionid transactiondate and so on 1 1/1/2007 ********* 2 1/2/2007 ********* 3 1/3/2007 ********* table 2 pk_rowid fk_transactionid description 1 1 record1 2 2 record2 3 3 record3 4 1 record4 5 2 record5 6 1 record6 result set should be like this pk_transactionid transactiondate description --> this is concatenated 1 1/1/2007 record1, record4, record6 2 1/2/2007 record2, record5 3 1/3/2007 record 6 thanks SlayerS_`BoxeR` + [ReD]NaDa
View Replies !
String Function Needed
I have a table with a column called Userdef. I is a user defined field. It looks like this ;;Polk;D-0002;;;;As you can see it is delimited by semicolons. I need to separate the semicolons into separate files like this Field1Field2Field2Field4Field5Field6<null><null>Polk<null>D-0002<null>How do I write this query in SQL Server?
View Replies !
LEFT And String Function
select left('Hello World /Ok',charindex('/','Hello World /Ok')-1)Hello WorldThat works fine.However I got an error message:select left('Hello World Ok',charindex('/','Hello World Ok')-1)Instead of:'Hello World Ok'I get:Server: Msg 536, Level 16, State 3, Line 1Invalid length parameter passed to the substring function.Microsoft Doc incorrectly says:"LEFT ( character_expression , integer_expression )integer_expressionIs a positive whole number. If integer_expression is negative, a nullstring is returned."Is there an easier solutoin using left or any other string functioninstead of using a case statement?Also, charindex('/','Hello World Ok') should return NULL instead 0 sothat we can use isnull function.Thanks.
View Replies !
String Function Questions
Hello All, I have am importing a file that contains an address field where the city and state are in the same field, IE ['Atlanta, GA']. How do I separate the city and state into separate fields. I can get the state by using the RIGHT function. My problem is getting the city information up to the comma. Any help would be appreciated. TIA
View Replies !
String Function Problem
We use a software program that concatenates 4 fields from one table and stores it into another table as the primary key, separating the four fields with a (char(26)). I need to parse out the strings to to get the fourth field. The only problem I'm having is that the first three fields are of different lengths so I can't use a substring function. An example of this is below: Row 1: VCS HPI 001 PENTAX K1000 Row 2: VCS KEYBOARDWO 001 NIKON N-60 Any help would be appreciated.
View Replies !
String Function - InStr
In VB there is InStr, in Excel you have SEARCH. what function can i use to search a string for a specific string. ie: how can i find where a space is in someones name? our database has a table that is used for security. it contains a user code, user name and passcode. i need to split the username (currently forename and surname in same field) into two, around the space. in VB i would write something like strFName = left(<usernamefield>,InStr(1,<usernamefield>," ")-1) to get the forename and similar to get the surname. how can i do this in SQL so that a view will supply the data apprpriately? one of the reasons i want to do this is so that i can sort the users by surname! another is so that i can give options on their usercode - a combination of forename initial and 2 characters from surname. any help welcomed.
View Replies !
Problem With String Function
Hi everybody, I have a weird problem with a string function. I use exactly the same query on to build a name field including firstname, evtl. prefix and lastname 2 different databases on the same server. All DB-options are the same. The design of the tables is exactly the same as are the datatypes. The query is as follows: SELECT RTRIM(FIRSTNAME) + ' '+ COALESCE(M.DESCN, '') + ' ' + LASTNAME AS NAME FROM DBO.PERSON LEFT JOIN MAINCODE M ON M.PREFIX = 'PRE' AND M.CODE = PERSON.PREFIX WHERE PERSONKEYFIELD = 20000009 While on database returns the name as expected g.e. "Markus Bohse", the second database returns the firstname plus an eventual prefix and then 100 empty spaces before the lastname. I'm lost. Has anybody else ever experienced something like this and knows the reason. I know I can use CASE as a workaround, but I still like to know what's going on here. Thanks a lot Markus
View Replies !
FINDSTRING String Function
I'm having issues trying to build an expression using the FINDSTRING function. Even when I use an example from BOL, I get an error upon clicking the "evaluate expression" button within expression builder saying "Cannot convert system.int32 to system.string". Here is my test expression... Code SnippetFINDSTRING("ABC", "A", 1) Here is the BOL expression... Code SnippetFINDSTRING("New York, NY, NY", "NY", 1) Both give me the same error message. I even tried casting it to an integer with no luck. Any ideas? Code Snippet(DT_I4) FINDSTRING("ABC" , "A" , 1)
View Replies !
Function To Parse A String
Hope someone can help... I need a function to parse a string using a beginning character parameter and an ending character parameter and extract what is between them. For example..... Here is the sample string: MFD-2fdr4.zip CREATE FUNCTION Parse(String, '-' , '.') ..... .... ..... END and it shoud return 2fdr4
View Replies !
SQL Search String Function
The 'LIKE' function looks for words that start with whatever is in the like condition. Is there an sql function similar but will look and compare at any part of the search string. For example I am using a webservice in dot net to populate a dropdown list using this sql SELECT compound_name FROM dbo.compound_name WHERE compound_name like @prefixText In this table there is a compound called SILCAP310 and I would like the search function to pick up 310 if I put this into the @prefix parameter. (but I would still like the search to perform like the 'LIKE' does also. SELECT compound_name FROM dbo.compound_name WHERE compound_name like @prefixText or compound_name SearchPartString @prefixText Thanks in advance
View Replies !
Inserting Text String Into Text File From DTS
I am currently using a DTS package to extractexport a SQLserver view to a fixed width textfile. My problem is that now I need to insert the date and and some other characters into the first line of the text file. The reasons for this are irrelevant, but its so another application can identify the file before it loads it. Somebody please help! I'm having a tought time finding a GOOD way to do this. I was hoping DTS has the ability, because I need to make this process manageable. Thanks.
View Replies !
MS SQL Function Return String - What Am I Doing Wrong?
Cannot see where I am going wrong. I always get a value of 0. I know my function works correctly, so it must be the VB. CREATE FUNCTION [dbo].[getNextProjectID] () RETURNS varchar(10) AS BEGIN '''''''''''''''''''........................... DECLARE @vNextProjectID varchar(10) RETURN @vNextProjectID END Sub LoadNextProjectNumber() Dim vProjectID As String Dim cmd As New SqlClient.SqlCommand() cmd.Connection = sqlConn cmd.CommandText = "getNextProjectID" cmd.Parameters.Add("@vNextProjectID", vProjectID) cmd.Parameters("@vNextProjectID").Direction = ParameterDirection.ReturnValue cmd.ExecuteScalar() vProjectID = cmd.Parameters("@vNextProjectID").Value txtProjectID.Text = vProjectID cmd.Dispose() End Sub
View Replies !
ISNULL Function And String Concatenation
Dear GroupJust wondered how I can avoid the CHAR(32) to be inserted if @String1 is NULL?SET @String3 = ISNULL(@String1,'') + CHAR(32) + ISNULL(@String2,'')Thanks very much for your expertise and efforts!Best Regards,Martin
View Replies !
Is There A Sql Database Function To Check Connection String?
Im working on a db library and I have everything working, what Im wanting to do is though is add a method that can check a connection string to make sure it is actually working. Right now, I have it doing a simple select * query to a particular table and returning true or false if an exception is caused. But I want to make the library as generic as possible, so is there another way to test teh connection string and tell if its working or not?Thanks,Cedric
View Replies !
Stripping Non-Numeric Characters From A String Function
Hi, 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?
View Replies !
Coma Separated String Value As Function Parameter
Hi Let€™s say I have employees table that contains id column for the supervisor of the employee. I need to create a function that gets coma separated string value of the supervisors€™ ids, And return the ids of employees that the ENTIRE listed supervisors are there supervisor. (some thing like €œSelect id from employees where supervisor=val_1 and supervisor=val_2 and €¦ and supervisor=val_N) Is there a way to create this function without using sp_exec? I€™ve created a function that splits the coma separated value to INT table. (For use in a function that do something like: €œSelect id from employees where supervisor in (select val from dbo.SplitToInt(coma_separated_value)) ) Thanks , Z
View Replies !
Passing Variable To String Compare In Function
I have created a function with: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[fn_concat_boxes](@item varchar, @week int) RETURNS VARCHAR(100) AS BEGIN DECLARE @Output varchar(100) SELECT @Output = COALESCE(@Output + '/', '') + CAST(quantity AS varchar(5)) FROM flexing_stock_transactions WHERE item = @item AND week = @week GROUP BY quantity ORDER BY quantity RETURN @Output END how can I pass the variable @item correctly for the string comparison WHERE item = @item AND week = @week to work correctly please? WHERE item = '@item' AND week = @week won't work and WHERE item = @item AND week = @week won't work.
View Replies !
String Or Binary Data Would Be Truncated. (only For 1700 Character String?)
I am trying to insert a row into a table of Microsoft SQL Server 2000. There are various columns. [SNO] [numeric](3, 0) NOT NULL , [DATT] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DATTA] [char] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [CODECS] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , The [DATTA] column is causing a problem. Even if I am trying to put only 1700 character string into [DATTA], the java code throws the following exception:- StaleConnecti A CONM7007I: Mapping the following SQLException, with ErrorCode 0 and SQLState 08S01, to a StaleConnectionException: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source) Why is it throwing an exception even though the sum-total of this row doesn't exceed 8000 characters? Can anyone please tell me what's wrong?
View Replies !
Strip Text From A String
I'm trying to find a way to strip text from a string. In the past (pre SQL Server) I would've used LName: Left(NCBH!Name,InStr(1,NCBH!NAME,",",1)-1) To strip the last name from a string like Franks,George J Apparently InStr is not a recognized function in SQL Server 2000. Or is it available but not in a view? Any thoughts would be greatly appreciated.
View Replies !
Inserting XML String (Text)
Hi SQL Gurus, I have an XML question to which I am kind of new. I have an XML string that I get by using a query similar to the following.... SELECT 1 AS Tag, NULL AS Parent, NULL AS [Zip_ROOT!1!], NULL AS [Zip!2!Value!element], NULL AS [Zip!2!State!element] FROM ZipCodes UNION SELECT 2, 1, NULL, Zip, State FROM ZipCodes where id < 3 FOR XML EXPLICIT This returns an XML string, which is very long. I need to take this string and INSERT/UPDATE to a Text column in a table. Any kind of help in this regard will be grately appreciated. Regards, Varma
View Replies !
Search For A String In Text
I am not very familiar with MS SQL, and I need to know how to search a table for a string. I know the column that it is in, but I don't know which record. I am looking for a small piece of text in a much larger text field (we are talkinga maybe a 10k char field and I am only looking for about a 15 char string). How do I do this with a MSSQL query? Thanks a bunch Nevermind, i found PATINDEX(). That worked great.
View Replies !
URGENT!!! Search Tool Function: Splitting A String
Hi All!!! I was tasked to come up with a search function and the content of the database given to me is in Chinese Characters. This would be my first time dealing with Chinese characters in the database and I need help with the following problem: The company wants to conduct the search in such a way that, instead of having the system read the entire sentence/phrase which the user keyed in as a SINGLE string, they want the Chinese Characters to be accessed individually, so that as long as any information in the database contains any one of the characters which the user have entered, they will be retrieved and returned. So how do I go about doing this? Does it have anything to do with Unicode? By the way, everything abt the search tool is working fine, I am just left with this dilemma of having the system recognise the entire sentence as ONE STRING, instead of conducting a search word by word or character by character. Anyway, the following is the SQL statement of my SQL Data Source which is bound to a Gridview displaying the returned results after a search is done...1 SELECT Name, Trans, Address1, Address1T, Address2, Address2T, City, CityT, CRPLID 2 FROM CRPL 3 WHERE (Trans LIKE '%' + @Trans + '%') OR 4 (Name LIKE '%' + @Name + '%') OR 5 (Address1 LIKE '%' + @Address1 + '%') OR 6 (Address1T LIKE '%' + @Address1T + '%') OR 7 (Address2 LIKE '%' + @Address2 + '%') OR 8 (Address2T LIKE '%' + @Address2T + '%') OR 9 (City LIKE '%' + @City + '%') OR 10 (CityT LIKE '%' + @CityT + '%') Thanks for all your help in advance!!!
View Replies !
Built In Function To Search The Occurence Of Char In String
Hi Everyone- i woder if someone know Built in function to search the occurence of char in string in T-SQL and iam not talking about the Index of the first occurence (e.g CHARINDEX ) but it is more generic i want to send to the function to ask about the index the second or the third or N occurence of the char. and i wonder if it is built in for performance reasons example getseachIndex("M|d|d","|",2)==> the result is 4
View Replies !
Adding String To Database, But Name Of String Is Added, Not Data
Hello, I am tring to add a string my database. Info is added, but it is the name of the string, not the data contained within. What am I doing wrong? The text "Company" and "currentUserID" is showing up in my database, but I need the info contained within the string. All help is appreciated! Imports System.Data Imports System.Data.Common Imports System.Data.SqlClientPartial Class _DefaultInherits System.Web.UI.Page Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser 'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true") 'First Command DataDim Company As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Company"), TextBox)).Text) Dim insertSQL1 As StringDim currentUserID As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox)).Text) insertSQL1 = "INSERT INTO Company (CompanyName, UserID) VALUES ('Company', 'currentUserID')"Dim cmd1 As New SqlCommand(insertSQL1, con) '2nd Command Data Dim selectSQL As String selectSQL = "SELECT companyKey FROM Company WHERE UserID = 'currentUserID'"Dim cmd2 As New SqlCommand(selectSQL, con) Dim reader As SqlDataReader '3rd Command Data Dim insertSQL2 As String insertSQL2 = "INSERT INTO Company_Membership (CompanyKey, UserID) VALUES ('CompanyKey', 'currentUserID')"Dim cmd3 As New SqlCommand(insertSQL2, con) 'First CommandDim added As Integer = 0 Try con.Open() added = cmd1.ExecuteNonQuery() lblResults.Text = added.ToString() & " records inserted."Catch err As Exception lblResults.Text = "Error inserting record." lblResults.Text &= err.Message Finally con.Close() End Try '2nd Command Try con.Open() reader = cmd2.ExecuteReader()Do While reader.Read() Dim CompanyKey = reader("CompanyKey").ToString() Loop reader.Close()Catch err As Exception lbl1Results.Text = "Error selecting record." lbl1Results.Text &= err.Message Finally con.Close() End Try '3rd Command Try con.Open() added = cmd3.ExecuteNonQuery() lbl2Results.Text = added.ToString() & " records inserted."Catch err As Exception lbl2Results.Text = "Error inserting record." lbl2Results.Text &= err.Message Finally con.Close()End Try End Sub End Class
View Replies !
Help: About Ms Sql Query, How Can I Check If A Part String Exists In A String?
Hello to all, I have a problem with ms sql query. I hope that somebody can help me. i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)). Example Datas for Table Relationships: IDMember Relationships . 3387 (2345, 2388,4567,....) 4567 (8990, 7865, 3387...) i wirte a query to check if there is Relationship between two members. Query: Declare @IDM int; Declare @IDO int; Set @IDM = 3387, @IDO = 4567; select * from Relationship where (IDMember = @IDM) and ( cast(@ID0 as char(100)) in (select Relationship .[RelationshipIDs] from Relationship where IDMember = @IDM)) But I get nothing by this query. Can Someone tell me where is the problem? Thanks Best Regards Pinsha
View Replies !
Need Help With String Manipulation - Splitting 1 String Into Multiple Columns
Hello All, I'm a non-programmer and an SQL newbie. I'm trying to create a printer usage report using LogParser and SQL database. I managed to export data from the print server's event log into a table in an SQL2005 database. There are 3 main columns in the table (PrintJob) - Server (the print server name), TimeWritten (timestamp of each print job), String (eventlog message containing all the info I need). My problem is I need to split the String column which is a varchar(255) delimited by | (pipe). Example: 2|Microsoft Word - ราย�ารรับ.doc|Sukanlaya|HMb1_SD_LJ2420|IP_192.10.1.53|82720|1 The first value is the job number, which I don't need. The second value is the printed document name. The third value is the owner of the printed document. The fourth value is the printer name. The fifth value is the printer port, which I don't need. The sixth value is the size in bytes of the printed document, which I don't need. The seventh value is the number of page(s) printed. How I can copy data in this table (PrintJob) into another table (PrinterUsage) and split the String column into 4 columns (Document, Owner, Printer, Pages) along with the Server and TimeWritten columns in the destination table? In Excel, I would use combination of FIND(text_to_be_found, within_text, start_num) and MID(text, start_num, num_char). But CHARINDEX() in T-SQL only starts from the beginning of the string, right? I've been looking at some of the user-defind-function's and I can't find anything like Excel's FIND(). Or if anyone can think of a better "native" way to do this in T-SQL, I've be very grateful for the help or suggestion. Thanks a bunch in advance, Chutikorn
View Replies !
Convert Text To String In SQL Query
Hi Everyone,My SQL Query is :"select Field1 from table1"The Field1 in Database is of Type "nvarchar" . I need to convert this Field into "integer" in the Query itself.Please Help!!I have already tried "Select convert(int, Field1 ) from table1"The field gives zero output ThanksRegardsNavdeep
View Replies !
Append String To Text Field....
I am trying to append some text to a full text field in SQL Server 2000. I tried this; but, it didn't work: UPDATE DefendantEventPros SET EventComment=EventComment + ' This event was completed on "& Date() &".' WHERE EventNumber="& eventnumber &" I get an error saying: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid operator for data type. Operator equals add, type equals text. How can I append some text to the end of a field? Thanks for any help!
View Replies !
Need Help Removing String Data From Text
I was wondering if someone could tell me how to use an SQL Function to remove everything from the mess of characters below except 'Test.' Really, any message could be substituted, so it's not as easy to leave everything except the 'Test.' string. { tf1ansiansicpg1252deff0deflang1033{fonttbl{f0fnilfcharset0 MS Shell Dlg 2;}{f1fnil MS Shell Dlg 2;}} {colortbl ; ed0green0lue0;} {*generator Msftedit 5.41.15.1507;}viewkind4uc1pard x720cf1f0fs20 Test.f1par } It's RichText. The fs20 could be fs24, fs25, etc. (any numbers). The end of the number would be the point to trim the beginning off. The '' seems like it would be the point to start trimming the end off. I've been experimenting with SUBSTRING together with CHARINDEX, but am just not getting even close to coming up with a solution. Any help would be greatly appreciated Thank you.
View Replies !
How To Convert Int To Text For Single String?
I wish to return a list of selected integers as a single string. So instead of getting 24 36 48 I get 24/36/48. My select query: SELECT CONVERT (varchar(10), quantity) + '/' FROM flexing_stock_transactions WHERE item = 'CH' AND week = 35 GROUP BY quantity ORDER BY quantity returns 24/ 36/ 48/ How can I achieve my goal please?
View Replies !
Converting A Text String To Datetime
Hello all, New to the forums here. I'm not a beginner with SQL, but nor am I a SQL developer - network engineer who knows some scripting and the fundamentals of DB design/administration. There's the background. I have a database of client information where a date is keyed in and stored as text. Because this is entered by end-users, the way it's entered varies - 1/1/2001, 01/01/2001, etc. Most use "1/1/2001" (note the date is not the same for each record) I have 4,000 records to update and I need to try to convert the text string to the correct date - changing 1/1/2005 to the same date in proper date/time format. My database uses datetime as an integer calculating the number of days from 12/30/1800. Today's date would be 75508. Time is separated into different fields. I can do the work to update the text to a single format to make the conversion easier, but I am having trouble locating the proper way to write a convert function to do this. I've searched online (which is how I came here) and have searched the forums without luck. Any help would be greatly appreciated!!!
View Replies !
|
TRACKER
