Trim SQL field

	Home Register Search Help Login

Sponsors

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Search Forums

Recent Posts

Todays Posts
Most Active posts
Posts since last visit
My Recent Posts
Mark posts read

Trim SQL field

View related threads: (in this forum | in all forums)

Logged in as: Guest

Users viewing this topic: none

Printable Version

All Forums >> Web Development >> ASP and Database >> Trim SQL field

Page: [1] 2 next > >>


Joe Kauffman Posts: 204 Joined: 6/9/2004 Status: offline	Trim SQL field - 1/18/2005 11:37:05 First off I want to thank Spooky for helping me figure out how to combine fields in SQL, now I need to be able to split a city, state field apart. I have a field in SQL that shows this format, "Denver, CO" or "Ouray, CO" and I need to be able to trim everything to the left of "CO" and display the results. I also need to be able to remove the last 4 characters for instance remove the ", CO" so I can just display the city. Any thoughts? _____________________________ Regards, Joe Kauffman


ou812 Posts: 1538 Joined: 1/5/2002 From: San Diego Status: offline	RE: Trim SQL field - 1/18/2005 13:09:47 Here is a page that shows the string functions and how to manipulate what you need: http://www.learnasp.com/learn/strings.asp _____________________________ -brian EnterpriseDB: Enterprise-class relational database management system PostgreSQL: The world's most advanced open source database (in reply to Joe Kauffman)


Joe Kauffman Posts: 204 Joined: 6/9/2004 Status: offline	RE: Trim SQL field - 1/18/2005 13:20:31 Thanks for the help Brian. But that shows how to return what I want in an ASP page. I'm trying to do this in SQL. My first thought was to do RIGHT(fieldname,2) to get the state information and then some sort of trim to get rid of the last 4 characters but the in the table, the field is specified as 40 length and I don't get anything when I do the Right(.... _____________________________ Regards, Joe Kauffman (in reply to ou812)


DesiMcK Posts: 440 Joined: 4/26/2004 From: Essex, UK Status: offline	RE: Trim SQL field - 1/18/2005 17:23:22 Would this work? <%=Left(fliedname,instr(fieldname,",")-1)%> instr(fieldname,",") would give you the numeric position of the comma. So then if you take one away and use LEFT to trim the string. Desi (in reply to Joe Kauffman)


Joe Kauffman Posts: 204 Joined: 6/9/2004 Status: offline	RE: Trim SQL field - 1/18/2005 17:28:25 Desi, Thanks for the input. That also looks more like ASP code than SQL. I'm trying to do this in a SQL view. _____________________________ Regards, Joe Kauffman (in reply to DesiMcK)


DesiMcK Posts: 440 Joined: 4/26/2004 From: Essex, UK Status: offline	RE: Trim SQL field - 1/18/2005 17:45:26 How about this then: <% dim citytrim citytrim = Left(fliedname,instr(fieldname,",")-1) %> mySQL = "SELECT * FROM MyTable WHERE City = " & citytrim Desi (in reply to Joe Kauffman)


Joe Kauffman Posts: 204 Joined: 6/9/2004 Status: offline	RE: Trim SQL field - 1/18/2005 17:48:17 Desi, I tried the first example in an ASP page and it works for getting the city name out. How will I need to modify it to get the state info if the field contains "City ,St". Would I simply need to to a Right( instead of Left( ? _____________________________ Regards, Joe Kauffman (in reply to DesiMcK)


DesiMcK Posts: 440 Joined: 4/26/2004 From: Essex, UK Status: offline	RE: Trim SQL field - 1/18/2005 17:53:37 No. I think you would need: <% dim statetrim statetrim= Right(fieldname,(Len(fieldname)-instr(fieldname,",")) %> Desi (in reply to Joe Kauffman)


Joe Kauffman Posts: 204 Joined: 6/9/2004 Status: offline	RE: Trim SQL field - 1/18/2005 17:56:38 Desi, I'm getting this: Microsoft VBScript compilation error '800a03ee' Expected ')' Is there a ")" missing? _____________________________ Regards, Joe Kauffman (in reply to DesiMcK)


Joe Kauffman Posts: 204 Joined: 6/9/2004 Status: offline	RE: Trim SQL field - 1/18/2005 17:58:31 Nevermind, I got it. It was missing a ')' at the end. I still need to figure out how to do this on my SQL database view. Thanks a ton for the help Desi. _____________________________ Regards, Joe Kauffman (in reply to Joe Kauffman)

Joe Kauffman

Posts: 204
Joined: 6/9/2004
Status: offline

RE: Trim SQL field - 1/20/2005 13:10:09

Thanks for the help Desi and sorry for the confusion. This works well in the SQL view and stored procedure I created. The only thing that I'm not sure about is that I am trying to pass a start date and end date in my asp page and call the stored procedure to give me any records that are between @startdate and @enddate. If it is in varchar can I still use it to filter for the date range I want? My view looks like this:

CREATE VIEW dbo.VW_APPTRPT
AS
SELECT     TOP 100 PERCENT dbo.HC1FLS_KCBUYR.KCBYLS AS BuyerLN, dbo.HC1FLS_KCBUYR.KCBYNM AS BuyerNM, 
                      dbo.HC1FLS_KCBUYR.KCBYMI AS BuyerMI, dbo.HC1FLS_KCBUYR.KCBYAD AS BuyerAD, dbo.HC1FLS_KCBUYR.KCBYCT AS BuyerCitySt, 
                      dbo.HC1FLS_KCBUYR.KCBYZC AS BuyerZip, dbo.HC1FLS_KCBUYR.KCBYHT AS BuyerHomePhone, 
                      dbo.HC1FLS_KCBUYR.KCBYWT AS BuyerWorkPhone, dbo.HC1FLS_KCBUYR.KCUNIT, dbo.HC1FLS_KCBUYR.KCJOB1, dbo.HC1FLS_KAMAST.KAPLAN, 
                      dbo.HC1FLS_KAMAST.KAELEV, dbo.HC1FLS_KCBUYR.KCSTAT, CAST(dbo.HC1FLS_KAMAST.KAMM21 AS varchar) 
                      + '/' + CAST(dbo.HC1FLS_KAMAST.KADD21 AS varchar) + '/' + CAST(dbo.HC1FLS_KAMAST.KACC21 AS varchar) 
                      + '0' + CAST(dbo.HC1FLS_KAMAST.KAYY21 AS varchar) AS FINALDATE, dbo.HC1FLS_KAMAST.KAMM21, dbo.HC1FLS_KAMAST.KADD21, 
                      dbo.HC1FLS_KAMAST.KACC21, dbo.HC1FLS_KAMAST.KAYY21
FROM         dbo.HC1FLS_KCBUYR INNER JOIN
                      dbo.HC1FLS_KAMAST ON dbo.HC1FLS_KCBUYR.KCJOB1 = dbo.HC1FLS_KAMAST.KAJOB1 AND 
                      dbo.HC1FLS_KCBUYR.KCUNIT = dbo.HC1FLS_KAMAST.KAUNIT
WHERE     (dbo.HC1FLS_KCBUYR.KCJOB1 > 100) AND (dbo.HC1FLS_KCBUYR.KCSTAT = 'SAL')
ORDER BY dbo.HC1FLS_KCBUYR.KCJOB1, dbo.HC1FLS_KCBUYR.KCUNIT

Thanks again for the help.

_____________________________

Regards,

Joe Kauffman

(in reply to Joe Kauffman)


rdouglass Posts: 9167 From: Biddeford, ME USA Status: offline	RE: Trim SQL field - 1/20/2005 15:50:58 quote: If it is in varchar can I still use it to filter for the date range I want? Dates in varchar fields will not sort/compare properly as dates. They must be date/time fields or converted first. _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to Joe Kauffman)


Joe Kauffman Posts: 204 Joined: 6/9/2004 Status: offline	RE: Trim SQL field - 1/20/2005 15:52:50 Thanks rdouglas. Then should I change this code somehow to convert it to datetime? CAST(dbo.HC1FLS_KAMAST.KAMM21 AS varchar) + '/' + CAST(dbo.HC1FLS_KAMAST.KADD21 AS varchar) + '/' + CAST(dbo.HC1FLS_KAMAST.KACC21 AS varchar) + '0' + CAST(dbo.HC1FLS_KAMAST.KAYY21 AS varchar) AS FINALDATE _____________________________ Regards, Joe Kauffman (in reply to rdouglass)


rdouglass Posts: 9167 From: Biddeford, ME USA Status: offline	RE: Trim SQL field - 1/20/2005 15:57:59 quote: CAST(dbo.HC1FLS_KAMAST.KAMM21 AS varchar) + '/' + CAST(dbo.HC1FLS_KAMAST.KADD21 AS varchar) + '/' + CAST(dbo.HC1FLS_KAMAST.KACC21 AS varchar) + '0' + CAST(dbo.HC1FLS_KAMAST.KAYY21 AS varchar) AS FINALDATE Have you tried something like this: CAST(((dbo.HC1FLS_KAMAST.KAMM21) + '/' + (dbo.HC1FLS_KAMAST.KADD21) + '/' + (dbo.HC1FLS_KAMAST.KACC21) + '0' + (dbo.HC1FLS_KAMAST.KAYY21)) AS varchar) AS FINALDATE Just a quick stab....hope it helps. _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to Joe Kauffman)


Joe Kauffman Posts: 204 Joined: 6/9/2004 Status: offline	RE: Trim SQL field - 1/20/2005 16:01:30 That looks like almost the same thing as I have? I placed it in and ran it and it says Error converting data type varchar to numeric. _____________________________ Regards, Joe Kauffman (in reply to rdouglass)


rdouglass Posts: 9167 From: Biddeford, ME USA Status: offline	RE: Trim SQL field - 1/20/2005 16:07:19 quote: CAST(((dbo.HC1FLS_KAMAST.KAMM21) + '/' + (dbo.HC1FLS_KAMAST.KADD21) + '/' + (dbo.HC1FLS_KAMAST.KACC21) + '0' + (dbo.HC1FLS_KAMAST.KAYY21)) AS varchar) AS FINALDATE OOOPS! Maybe this: (told you I didn't test it) CAST(((dbo.HC1FLS_KAMAST.KAMM21) + '/' + (dbo.HC1FLS_KAMAST.KADD21) + '/' + (dbo.HC1FLS_KAMAST.KACC21) + '0' + (dbo.HC1FLS_KAMAST.KAYY21)) AS date/time) AS FINALDATE Although you may have better luck with CONVERT: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to Joe Kauffman)


Joe Kauffman Posts: 204 Joined: 6/9/2004 Status: offline	RE: Trim SQL field - 1/20/2005 16:14:03 I tried it and it says Line1: Incorrect syntax near (/). I'm assuming it is the '/' between Date / Time. < Message edited by Joe Kauffman -- 1/20/2005 16:27:47 > _____________________________ Regards, Joe Kauffman (in reply to rdouglass)


rdouglass Posts: 9167 From: Biddeford, ME USA Status: offline	RE: Trim SQL field - 1/20/2005 16:27:29 Have you looked at the link I posted? Again, you may have to CONVERT and not CAST. _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to Joe Kauffman)


rdouglass Posts: 9167 From: Biddeford, ME USA Status: offline	RE: Trim SQL field - 1/20/2005 16:28:35 quote: CAST(((dbo.HC1FLS_KAMAST.KAMM21) + '/' + (dbo.HC1FLS_KAMAST.KADD21) + '/' + (dbo.HC1FLS_KAMAST.KACC21) + '0' + (dbo.HC1FLS_KAMAST.KAYY21)) AS date/time) AS FINALDATE Or maybe: CAST(((dbo.HC1FLS_KAMAST.KAMM21) & '/' & (dbo.HC1FLS_KAMAST.KADD21) & '/' & (dbo.HC1FLS_KAMAST.KACC21) & '0' & (dbo.HC1FLS_KAMAST.KAYY21)) AS date/time) AS FINALDATE _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to rdouglass)


Joe Kauffman Posts: 204 Joined: 6/9/2004 Status: offline	RE: Trim SQL field - 1/20/2005 16:33:32 rdouglas, thanks again for taking the time to help me. I tried the: CAST(((dbo.HC1FLS_KAMAST.KAMM21) & '/' & (dbo.HC1FLS_KAMAST.KADD21) & '/' & (dbo.HC1FLS_KAMAST.KACC21) & '0' & (dbo.HC1FLS_KAMAST.KAYY21)) AS date/time) AS FINALDATE and it still gives me an error regarding the '/' between date/time. If I try taking this out it says "Invalid operator for data type. Operator equals boolean AND, type equals decimal." I looked at the link you gave and the convert is somewhat new to me so I'll have to try to figure out what to do with it. I've learned a ton from this forum. Thanks again to everyone. _____________________________ Regards, Joe Kauffman (in reply to rdouglass)


Joe Kauffman Posts: 204 Joined: 6/9/2004 Status: offline	RE: Trim SQL field - 1/21/2005 11:31:34 Man I'm thoroughly cornfused now. I've tried several convert statements and nothing seems to work! All I keep getting is error converting data type varchar to numeric. Any thoughts? _____________________________ Regards, Joe Kauffman (in reply to Joe Kauffman)


rdouglass Posts: 9167 From: Biddeford, ME USA Status: offline	RE: Trim SQL field - 1/21/2005 11:40:40 quote: converting data type varchar to numeric Why are you trying to covert to numeric? I thought you were doing a Date/Time comparison? _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to Joe Kauffman)


Joe Kauffman Posts: 204 Joined: 6/9/2004 Status: offline	RE: Trim SQL field - 1/21/2005 11:45:37 I am that's what is so confusing. I've been working only with datetime. _____________________________ Regards, Joe Kauffman (in reply to rdouglass)


rdouglass Posts: 9167 From: Biddeford, ME USA Status: offline	RE: Trim SQL field - 1/21/2005 11:57:16 OK. I built a test table in SQL Server and populated 3 varchar fields with numerical equivalents with month,day, and year numbers and ran this VIEW: SELECT CAST(Text1 + '/' + Text2 + '/' + Text3 AS DateTime) AS Expr1 FROM dbo.tblTestingOF and was returned valid dates. Sorry 'bout the earlier syntax issue. It does look like the CAST verb will work for you. There may be other issues but CAST should return a valid date to compare to. Does this get you any closer? _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to Joe Kauffman)


Joe Kauffman Posts: 204 Joined: 6/9/2004 Status: offline	RE: Trim SQL field - 1/21/2005 12:09:36 Ok, I tried using this: CAST(KAMM21 + '/' + KADD21 + '/' + KACC21 + '0' + KAYY21 AS DateTime) AS FINALDATE and it still gives me the same varchar to numeric error. I don't know if it makes any difference but the fields are decimal type in the table I'm pulling them from. When I remove the '/' and '0' it will run but only gives me 1/01/1900. _____________________________ Regards, Joe Kauffman (in reply to rdouglass)


rdouglass Posts: 9167 From: Biddeford, ME USA Status: offline	RE: Trim SQL field - 1/21/2005 12:50:03 quote: fields are decimal type in the table Oh. I was under the distinct impression that they were numerals only in a varchar field. Can I ask why they're in decimal format? Is that a requirement somewhere else? Maybe I don't understand the Q: I thought you were basically rebuilding the date from 3 fields; a month value, a day value, and a year value. Am I mistaken? And you want to do a date compare on the results correct? _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to Joe Kauffman)


Joe Kauffman Posts: 204 Joined: 6/9/2004 Status: offline	RE: Trim SQL field - 1/21/2005 13:09:49 The fields are imported from a DTS package from our AS400. My initial intent was to be able to compare a start and end date that a user provides in the asp page and select any records that are between @startdate and @enddate. The fields coming in from the AS400 are the month (KAMM21), day (KADD21), century (KACC21) and year (KAYY21). I was hoping to combine these into one field to test against. Do you think there is a better way without having to combine them? I appologize if I was vague or misleading. Hopefully this clears it up for you. _____________________________ Regards, Joe Kauffman (in reply to rdouglass)


rdouglass Posts: 9167 From: Biddeford, ME USA Status: offline	RE: Trim SQL field - 1/21/2005 13:21:08 quote: The fields coming in from the AS400 are the month (KAMM21), day (KADD21), century (KACC21) and year (KAYY21). Can you give me 1 or 2 examples of what a typical dataset being returned might look like? You said decimal format, are these fields being returned as numbers (as opposed to characters)? _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to Joe Kauffman)


Joe Kauffman Posts: 204 Joined: 6/9/2004 Status: offline	RE: Trim SQL field - 1/21/2005 14:40:36 Here's a couple of examples: KAMM21 KADD21 KACC21 KAYY21 1 4 20 5 2 3 20 5 1 12 20 5 _____________________________ Regards, Joe Kauffman (in reply to rdouglass)


rdouglass Posts: 9167 From: Biddeford, ME USA Status: offline	RE: Trim SQL field - 1/21/2005 14:52:54 quote: SELECT CAST(Text1 + '/' + Text2 + '/' + Text3 AS DateTime) AS Expr1 FROM dbo.tblTestingOF So with my example from above and converting those fields to int, I used this: SELECT CAST(CAST(Text1 AS VARCHAR) + '/' + CAST(Text2 AS VARCHAR) + '/' + CAST(Text3 AS VARCHAR) AS DateTime) AS Expr1 FROM dbo.tblTestingOF to return Date values. SO with yours maybe something like: CAST(CAST(KAMM21 AS VARCHAR) + '/' + CAST(KADD21 AS VARCHAR) + '/' + CAST(KACC21 AS VARCHAR) + '0' + CAST(KAYY21 AS VARCHAR) AS DateTime) AS FINALDATE That confusing enough? _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to Joe Kauffman)

Page: [1] 2 next > >>

All Forums >> Web Development >> ASP and Database >> Trim SQL field

Page: [1] 2 next > >>

Jump to: 1

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts