Change Data Display - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Change Data Display

Author

Topic

vree
Starting Member

30 Posts

Posted - 2005-09-28 : 16:42:02

I have a db; that stores a company name; if the end user types in
The Stoneware Jar Company

I would like to display:
Stoneware Jar Company

All company names entered like that with no preceding "The"

How can I display something diff on my ASP Page? Possible?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-28 : 16:51:48

you can use the replace function.
just replace "the " with "".
There are plenty examples in books online

Duane.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-29 : 01:17:17

Try this also

Select Case when column like 'The%' then substring(column,5,len(column)) else column end
from yourTable

Madhivanan

Failing to plan is Planning to fail

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-29 : 02:05:25

Yip on second thought's madhivanan's solution would be better, as the replace function will replace any occurences of "the" ie "Bathe " would become "Ba".

Duane.

vree
Starting Member

30 Posts

Posted - 2005-09-29 : 02:33:32

Ok forgive me for being really new:

Here is an example of the page; i have a list of company names returned via:

.write "<span class='bdyinfo'>" & rs("entName") & "</span>"

http://www.nysta.com/reg/pta/attendees.asp

The president is having a hissy; because The Middleburgh Telephone company; is in the "T's" he wants it in the "M's" Its 2am here, been reading on Reg Expressions but don't really know how to handle the stuff when it is not just a string; I am a beginner. Thank you for your help

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-29 : 02:39:34

What don't you understand?

CREATE TABLE #Company
(
entName VARCHAR(100)
)

INSERT INTO #Company VALUES('The Middleburgh Telephone company')

Select Case when entName like 'The%' then substring(entName,5,len(entName)) else entName end
from #Company

Duane.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-29 : 02:40:52

Did you try the method I suggested?

Madhivanan

Failing to plan is Planning to fail

vree
Starting Member

30 Posts

Posted - 2005-09-29 : 02:46:17

I appreciate both of you trying to help me; and I must beg your patience. This is one of the first ASP pages I have ever done. It is very simple. A simple SQL query that returns Company Name, First name and last name. I simple don't know what to do to this piece of code:

<%
sql = "SELECT e.entName, c.contFName, c.contLName FROM dbo.regEntity e INNER JOIN regContacts c ON e.entId = c.entId INNER JOIN regAttendees a ON c.contId = a.contId INNER JOIN regContTypes t ON c.contId = t.contId WHERE (t.contType = 'Booth Rep' OR t.contType = 'Spons Rep' OR t.contType = 'Attendee Rep'OR t.contType = 'Attendee') AND (a.meetId = 13) ORDER BY e.entName "

'response.write(sql)
set rs = conn.execute(sql)
if rs.bof or rs.eof then
msg = "Error - no contact found."
else
with response
.write "<div align ='center'><table border='0'>"
do until rs.eof
.write "<tr>"
.write "<td width='170'>"
.write "<span class='bdyinfo'>" & rs("contFName") & " " & rs("contLName") & "</span>"
.write "</td>"
.write "<td width='200'>"
.write "<span class='bdyinfo'>" & rs("entName") & "</span>"
.write "</td>"
.write "</tr>"
rs.movenext
loop
.write "</table></div>"
end with
end if
rs.close
set rs = nothing
%>

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-29 : 02:53:21

SELECT Case when entName like 'The%' then substring(entName,5,len(entName)) else entName end, c.contFName, c.contLName FROM dbo.regEntity e INNER JOIN regContacts c ON e.entId = c.entId INNER JOIN regAttendees a ON c.contId = a.contId INNER JOIN regContTypes t ON c.contId = t.contId WHERE (t.contType = 'Booth Rep' OR t.contType = 'Spons Rep' OR t.contType = 'Attendee Rep'OR t.contType = 'Attendee') AND (a.meetId = 13) ORDER BY Case when entName like 'The%' then substring(entName,5,len(entName)) else entName end

Duane.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-29 : 02:55:37

One other thing...... It's better practice using stored procs as opposed to inline sql - it's safer and easier to maintain.

Duane.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-29 : 02:55:39

If rs("contFName") has the word "The" then use try this

<%
sql = "SELECT e.entName, c.contFName, c.contLName FROM dbo.regEntity e INNER JOIN regContacts c ON e.entId = c.entId INNER JOIN regAttendees a ON c.contId = a.contId INNER JOIN regContTypes t ON c.contId = t.contId WHERE (t.contType = 'Booth Rep' OR t.contType = 'Spons Rep' OR t.contType = 'Attendee Rep'OR t.contType = 'Attendee') AND (a.meetId = 13) ORDER BY e.entName "

'response.write(sql)
set rs = conn.execute(sql)
if rs.bof or rs.eof then
msg = "Error - no contact found."
else
with response
.write "<div align ='center'><table border='0'>"
do until rs.eof
.write "<tr>"
.write "<td width='170'>"
if left(rs("contFName"),3)="The" then
.write "<span class='bdyinfo'>" & mid(rs("contFName"),5,len(rs("contFName")) & " " & rs("contLName") & "</span>"
else
.write "<span class='bdyinfo'>" & rs("contFName") & " " & rs("contLName") & "</span>"
end if
.write "</td>"
.write "<td width='200'>"
.write "<span class='bdyinfo'>" & rs("entName") & "</span>"
.write "</td>"
.write "</tr>"
rs.movenext
loop
.write "</table></div>"
end with
end if
rs.close
set rs = nothing
%>

If mid is not supported then use Substring

Madhivanan

Failing to plan is Planning to fail

vree
Starting Member

30 Posts

Posted - 2005-09-29 : 02:56:32

Ok than you for that, where do I put it?

vree
Starting Member

30 Posts

Posted - 2005-09-29 : 02:58:59

Madhivanan
Thank you!!!!! I hope this works will try immediately; its entName thats giving me the prob but i think i can edit that code will try; See i didnt know you could do the IF, string manip; right in the middle of that code.
will let you know momentarily
victoria

vree
Starting Member

30 Posts

Posted - 2005-09-29 : 03:08:44

Oops not so good! I tried this:
if left(rs("entName"),3)="The" then
.write "<span class='bdyinfo'>" & & mid(rs("entName"), 5,len(rs("entName"))& "</span>"
else
.write "<span class='bdyinfo'>" & rs("entName") & "</span>"
end if

which promptly failed. I am sure it is something i did trying to change it
http://www.nysta.com/reg/pta/attendees.asp

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-29 : 03:11:23

you have 2 "&"'s remove the one just after "<span class='bdyinfo'>"

Duane.

vree
Starting Member

30 Posts

Posted - 2005-09-29 : 03:12:56

http://www.nysta.com/reg/pta/attendees.asp

did it; still no go did i do it right?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-29 : 03:15:54

What did you get error or wrong result?

Madhivanan

Failing to plan is Planning to fail

vree
Starting Member

30 Posts

Posted - 2005-09-29 : 03:16:41

Microsoft VBScript compilation error '800a03ee'

Expected ')'

/reg/pta/attendees.asp, line 56

.write "<span class='bdyinfo'>" & mid(rs("entName"), 5,len(rs("entName"))& "</span>"

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-29 : 03:18:33

.write "<span class='bdyinfo'>" & mid(rs("entName"), 5,len(rs("entName")))& "</span>"

Duane.

vree
Starting Member

30 Posts

Posted - 2005-09-29 : 03:21:25

OH MY God Thank you guys that worked!!!!! If i get a raise, I owe you!
However one last question, it got rid of "The" but now i seem to have no sort order?
http://www.nysta.com/reg/pta/attendees.asp

it was sorted on e.entName in the SQL Statement
I mean, they are sorted according to the way they are in the db; not the way they are now on the page?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-29 : 03:24:53

The sort order problem can be solved by using the sql code I posted earlier and perhaps rather using it in a stored proc than inline sql - I emailed you a solution :)

Duane.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources