Finding last instance of string

How can I find the last instance of a string? I was thinking of writing a
loop that goes through the string, but that requires writing a few lines of
code. I was also thinking of inverting the string (in this case, charindex
would work since). But I'm not sure how to invert the string w/o writing too
much code.
Any help is appreciated. Thanks.> But I'm not sure how to invert the string w/o writing too
> much code.
Check the REVERSE T-SQL function.
Dejan Sarka|||DECLARE @.s VARCHAR(32);
SET @.s = 'aoodfsdf';
SELECT LastInstanceOfA = CASE CHARINDEX('a', @.s)
WHEN 0 THEN 0
ELSE LEN(@.s) + 1 - CHARINDEX('a', REVERSE(@.s))
END;
"VMI" <VMI@.discussions.microsoft.com> wrote in message
news:109B0CEE-6424-4154-9ED3-7E56C2B41B7F@.microsoft.com...
> How can I find the last instance of a string? I was thinking of writing a
> loop that goes through the string, but that requires writing a few lines
> of
> code. I was also thinking of inverting the string (in this case, charindex
> would work since). But I'm not sure how to invert the string w/o writing
> too
> much code.
> Any help is appreciated. Thanks.

Finding last "whitespace" character in a string?

I'm trying to figure out how to find the last whitespace character in a
varchar string. To complicate things, it's not just spaces that I'm
looking for, but certain ascii characters (otherwise, obviously, just
use LEN). My initial thought was to REVERSE it, find the location
(using CHARINDEX) looking for each of those characters (so, multiple
queries), then subtract that from the LEN of the string.

The problem I'm running into is that there are about a dozen different
characters we're looking for. Any suggestions? My thought was to
(this sounds silly, so there's gotta be a better way) dump the results
from each CHARINDEX into a table, then find the MAX of the table and
use that. But, like I said, it sounds silly. I don't think I can do a
[^0-9A-Z] either, since there are non-Alphanumeric characters we're
looking for.

Many thanks."M Bourgon" wrote:

> I'm trying to figure out how to find the last whitespace character in a
> varchar string. To complicate things, it's not just spaces that I'm
> looking for, but certain ascii characters (otherwise, obviously, just
> use LEN). My initial thought was to REVERSE it, find the location
> (using CHARINDEX) looking for each of those characters (so, multiple
> queries), then subtract that from the LEN of the string.
> The problem I'm running into is that there are about a dozen different
> characters we're looking for. Any suggestions? My thought was to
> (this sounds silly, so there's gotta be a better way) dump the results
> from each CHARINDEX into a table, then find the MAX of the table and
> use that. But, like I said, it sounds silly. I don't think I can do a
> [^0-9A-Z] either, since there are non-Alphanumeric characters we're
> looking for.
> Many thanks.

Why not use LIKE but build the pattern in a variable using CHAR()?

declare @.t table (c varchar(50))

insert @.t values ('not this one')
insert @.t values ('or this one')

insert @.t values ('
not even this one')

insert @.t values ('only this one
')

declare @.crit varchar(50)
set @.crit = '%[' + CHAR(13) + CHAR(10) + ']'

select * from @.t where c like @.crit

Craig|||How about the old table of numbers trick?

first you create a table of numbers big enough to handle the length of
string you are dealing with, I'll do 8000 in this case but it could be more
(You only need to do this the once)

SELECT TOP 8000 Number = IDENTITY(int, 1, 1)
INTO Numbers
FROM master..sysobjects, master..sysobjects, master..sysobjects

Now that you have your table of numbers, you can use it to index into your
string and look for the whitespace:

SELECT Top 1 Number
FROM Numbers
WHERE Number<=Len(@.Str) AND Substring(@.Str, Number, 1) IN (char(32),
char(13), char(8))
ORDER BY Number DESC

'Get the first number from the table where the number is less or equal to
the length of the string and the character at the numbers position is in a
given set of whitespace characters, starting at the highest number'

For optimum performance create a clustered index on the table of numbers.

You may also be able to use REVERSE and PATINDEX by encoding a string of all
the whitespace characters '%['+char(8)+char(10)+char(32)+']%', although I've
never tried using '[]' with patindex and its not nearly as interesting :)

Mr Tea

"M Bourgon" <bourgon@.gmail.com> wrote in message
news:1111101450.978587.314110@.l41g2000cwc.googlegr oups.com...
> I'm trying to figure out how to find the last whitespace character in a
> varchar string. To complicate things, it's not just spaces that I'm
> looking for, but certain ascii characters (otherwise, obviously, just
> use LEN). My initial thought was to REVERSE it, find the location
> (using CHARINDEX) looking for each of those characters (so, multiple
> queries), then subtract that from the LEN of the string.
> The problem I'm running into is that there are about a dozen different
> characters we're looking for. Any suggestions? My thought was to
> (this sounds silly, so there's gotta be a better way) dump the results
> from each CHARINDEX into a table, then find the MAX of the table and
> use that. But, like I said, it sounds silly. I don't think I can do a
> [^0-9A-Z] either, since there are non-Alphanumeric characters we're
> looking for.
> Many thanks.|||Oops,
dont forget to alias the tables in the cross join, if you dont have access
to sysobjects you can use any table with a decent amount of records.

master..sysobjects a, master..sysobjects b, master..sysobjects c

Mr Tea

"Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
news:l2w_d.814$MO6.640@.newsfe2-gui.ntli.net...
> How about the old table of numbers trick?
> first you create a table of numbers big enough to handle the length of
> string you are dealing with, I'll do 8000 in this case but it could be
> more (You only need to do this the once)
> SELECT TOP 8000 Number = IDENTITY(int, 1, 1)
> INTO Numbers
> FROM master..sysobjects, master..sysobjects, master..sysobjects
> Now that you have your table of numbers, you can use it to index into your
> string and look for the whitespace:
> SELECT Top 1 Number
> FROM Numbers
> WHERE Number<=Len(@.Str) AND Substring(@.Str, Number, 1) IN (char(32),
> char(13), char(8))
> ORDER BY Number DESC
> 'Get the first number from the table where the number is less or equal to
> the length of the string and the character at the numbers position is in a
> given set of whitespace characters, starting at the highest number'
> For optimum performance create a clustered index on the table of numbers.
> You may also be able to use REVERSE and PATINDEX by encoding a string of
> all the whitespace characters '%['+char(8)+char(10)+char(32)+']%',
> although I've never tried using '[]' with patindex and its not nearly as
> interesting :)
> Mr Tea
> "M Bourgon" <bourgon@.gmail.com> wrote in message
> news:1111101450.978587.314110@.l41g2000cwc.googlegr oups.com...
>> I'm trying to figure out how to find the last whitespace character in a
>> varchar string. To complicate things, it's not just spaces that I'm
>> looking for, but certain ascii characters (otherwise, obviously, just
>> use LEN). My initial thought was to REVERSE it, find the location
>> (using CHARINDEX) looking for each of those characters (so, multiple
>> queries), then subtract that from the LEN of the string.
>>
>> The problem I'm running into is that there are about a dozen different
>> characters we're looking for. Any suggestions? My thought was to
>> (this sounds silly, so there's gotta be a better way) dump the results
>> from each CHARINDEX into a table, then find the MAX of the table and
>> use that. But, like I said, it sounds silly. I don't think I can do a
>> [^0-9A-Z] either, since there are non-Alphanumeric characters we're
>> looking for.
>>
>> Many thanks.
>>|||On 17 Mar 2005 15:17:31 -0800, M Bourgon wrote:

>I'm trying to figure out how to find the last whitespace character in a
>varchar string. To complicate things, it's not just spaces that I'm
>looking for, but certain ascii characters (otherwise, obviously, just
>use LEN). My initial thought was to REVERSE it, find the location
>(using CHARINDEX) looking for each of those characters (so, multiple
>queries), then subtract that from the LEN of the string.
>The problem I'm running into is that there are about a dozen different
>characters we're looking for. Any suggestions?

Hi M,

Yep - use PATINDEX instead of CHARINDEX. In the example below, I search
for space and char(8) (tab) only, but it's easy to add other whitespace
characters. To test it, run the code below, uncomment the commented line
and run it again - you'll see that first the space, then the tabl is
found.

declare @.a varchar(100)
set @.a = 'This is a test'
-- + char(8) + 'tabbed'
select @.a
declare @.LastWhite int
set @.LastWhite = len(@.a) - patindex('%[ ' + char(8) + ']%', reverse(@.a))
+ 1
select @.LastWhite
select substring(@.a, @.LastWhite, 99)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||D'oh! That's exactly what I was looking for. Many thanks, everyone.|||Okay, I thought that was the answer, but not quite. My problem is that
I'm looking to send back the "this is a test", even if it's technically
'this is a test ' + char(8) + char(8). Patindex will send the first
instance, which in this case would mean there's still a char(8) & a
space left. I saw some code that does something like this, where it
uses a while loop to step through the table, but I'd rather avoid that
if possible.

I'll give Lee's "table of Numbers" trick a shot next. Thanks, all.|||On 18 Mar 2005 07:18:21 -0800, M Bourgon wrote:

>Okay, I thought that was the answer, but not quite. My problem is that
>I'm looking to send back the "this is a test", even if it's technically
>'this is a test ' + char(8) + char(8). Patindex will send the first
>instance, which in this case would mean there's still a char(8) & a
>space left. I saw some code that does something like this, where it
>uses a while loop to step through the table, but I'd rather avoid that
>if possible.
>I'll give Lee's "table of Numbers" trick a shot next. Thanks, all.

Hi M,

So I guess that you're not looking for the last whitespace, but for the
last non-whitespace? Very easy to do - just add one caret (^) to my
code:

declare @.a varchar(100)
set @.a = 'This is a test ' + char(8) + char(8)
select @.a
declare @.LastWhite int
set @.LastWhite = len(@.a) - patindex('%[^ ' + char(8) + ']%',
reverse(@.a)) + 1
select @.LastWhite
select substring(@.a, @.LastWhite, 99)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Aha! Boy, am I dense.

One correction, for future generations reading this: change the len(@.a)
to datalength(@.a), in case all that's at the end are spaces (since it's
a varchar, it'll automatically drop spaces at the end). You could
probably change it to a char(100) as well, but this way you're
(hopefully) not using up as much memory.

Thanks again, Hugo.|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> Yep - use PATINDEX instead of CHARINDEX. In the example below, I search
> for space and char(8) (tab) only,

The nit-picking department like to point out that char(8) is backspace. Tab
is char(9).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Sat, 19 Mar 2005 18:03:07 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
>> Yep - use PATINDEX instead of CHARINDEX. In the example below, I search
>> for space and char(8) (tab) only,
>The nit-picking department like to point out that char(8) is backspace. Tab
>is char(9).

Hi Erland,

As someone who occasionaly uses ^H in messages to sufficiently geeky
persons, I really should have known that...

Thanks for picking my nit!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Finding data

Is it possible to search an entire database for a string or number? If so
How?
- Hamilton
Here is one option: sp_grep
http://examples.oreilly.com/wintrnssql/readme.txt
--=20
Keith
"Hamilton" <hamilton@.polese.com> wrote in message =
news:%23mu9fm3FEHA.3252@.TK2MSFTNGP11.phx.gbl...
> Is it possible to search an entire database for a string or number? If =
so
> How?
>=20
> - Hamilton
>=20
>

finding connection string

to find a connection string I use to create a text file and change the
extension to ?
and then I could connect to the database and find the connection string. I
forgot what the file extension was. any one knows?
Thanks
It was UDL, I remember now.
"me" wrote:

> to find a connection string I use to create a text file and change the
> extension to ?
> and then I could connect to the database and find the connection string. I
> forgot what the file extension was. any one knows?
> Thanks

finding connection string

to find a connection string I use to create a text file and change the
extension to ?
and then I could connect to the database and find the connection string. I
forgot what the file extension was. any one knows?
ThanksIt was UDL, I remember now.
"me" wrote:

> to find a connection string I use to create a text file and change the
> extension to ?
> and then I could connect to the database and find the connection string. I
> forgot what the file extension was. any one knows?
> Thanks

finding connection string

to find a connection string I use to create a text file and change the
extension to ?
and then I could connect to the database and find the connection string. I
forgot what the file extension was. any one knows?
ThanksIt was UDL, I remember now.
"me" wrote:
> to find a connection string I use to create a text file and change the
> extension to ?
> and then I could connect to the database and find the connection string. I
> forgot what the file extension was. any one knows?
> Thanks

finding a string somewhere in the MS SQL user tables

Hi,
I have an application which shows a certain piece of data on the
screen, but I do not know which table it comes from (281 user tables).
How can I easily find which table has this data ?
(without opening each table and scanning through 1000's of rows
manually)
TIA
MichaelUse the profiler ... do the activity again to have a look at that
data ... In the backend it would definately show you the table or the
SP the application is calling ... Dig in further.
Regards
Bharat Butani.
On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael|||If you now the column name you can do a search with object browser in query
analyzer on the database.
--
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"fauxDBA@.gmail.com" wrote:

> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
>
> Regards
> Bharat Butani.
>
> On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
>
>|||On 4 May, 13:47, faux...@.gmail.com wrote:[vbcol=seagreen]
> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
> Regards
> Bharat Butani.
> On May 4, 3:39 pm,ChikenKoma<michaelnewp...@.yahoo.com> wrote:
>
>
>
>
thanks I will try that|||On 4 May, 13:57, Hate_orphaned_users
<Hateorphanedus...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> If you now the column name you can do a search with object browser in quer
y
> analyzer on the database.
> --
> I drank alot of beer and ended up in the police department database.
> Drank more beer and learned SQL in the dark hours.
> DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
> I love SQL
> "faux...@.gmail.com" wrote:
>
>
>
>
>
thats the problem we dont know the table, in Ingres I used to dump the
whole database to ascii files and do a string search, this would
highlight a file which corresponded to a table, then I could search
probable columns in that table.|||The simplest way is the probably just to query the cataloguef or the tables,
processing the results to create select statement for each of the tables,
then run them until you find the column.
"Chiken Koma" <michaelnewport@.yahoo.com> wrote in message
news:1178275148.581808.186800@.l77g2000hsb.googlegroups.com...
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael
>|||If it just is the one time thing to search for a string, below is a SP
which I had written a long time back for fun (this is exactly similar
to what Mark had suggested). Use it rarely, and also not at the peak
time of the day.
----
---
DROP PROCEDURE FIND_IN_DB
GO
CREATE PROCEDURE FIND_IN_DB
@.SEARCHSTR VARCHAR(100),
@.EXACT_MATCH VARCHAR(1) = 'F',
@.MATCH_FULL_WORD VARCHAR(1) = 'F'
AS
BEGIN
DECLARE @.FROM INT
DECLARE @.TO INT
DECLARE @.TABLE_ID INT
DECLARE @.TABLE_NAME SYSNAME
DECLARE @.COLUMN_NAME SYSNAME
DECLARE @.OPERATOR SYSNAME
CREATE TABLE #TEMP_TABLE (
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TABLE_ID INT,
TABLE_NAME SYSNAME)
CREATE TABLE ##RESULT(
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TYPE VARCHAR(15),
TABLE_NAME VARCHAR(100),
COLUMN_NAME VARCHAR(300),
DATA_TEXT VARCHAR(7000) )
INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
BY NAME
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME = @.SEARCHSTR
SET @.FROM = 1
SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
IF @.EXACT_MATCH = 'F'
BEGIN
IF @.MATCH_FULL_WORD = 'T'
SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
ELSE
SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
SELECT @.OPERATOR = ' LIKE '
END
ELSE
SELECT @.OPERATOR = ' = '
WHILE @.FROM <= @.TO
BEGIN
SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
#TEMP_TABLE WHERE SLNO = @.FROM
SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME = @.SEARCHSTR
WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
BY COLUMN_NAME)
BEGIN
SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
#TEMP_COLUMNS ORDER BY COLUMN_NAME)
EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
DATA_TEXT)
SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
+ ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
+ @.OPERATOR + '''' + @.SEARCHSTR + '''')
DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
END
DROP TABLE #TEMP_COLUMNS
SET @.FROM = @.FROM + 1
END
SELECT * FROM ##RESULT
DROP TABLE #TEMP_TABLE
DROP TABLE ##RESULT
END
GO
-- EXEC FIND_IN_DB 'test%', 'F', 'T'
----
---|||On 6 May, 12:57, nime...@.gmail.com wrote:
> If it just is the one time thing to search for a string, below is a SP
> which I had written a long time back for fun (this is exactly similar
> to what Mark had suggested). Use it rarely, and also not at the peak
> time of the day.
> ----
---
> DROP PROCEDURE FIND_IN_DB
> GO
> CREATE PROCEDURE FIND_IN_DB
> @.SEARCHSTR VARCHAR(100),
> @.EXACT_MATCH VARCHAR(1) = 'F',
> @.MATCH_FULL_WORD VARCHAR(1) = 'F'
> AS
> BEGIN
> DECLARE @.FROM INT
> DECLARE @.TO INT
> DECLARE @.TABLE_ID INT
> DECLARE @.TABLE_NAME SYSNAME
> DECLARE @.COLUMN_NAME SYSNAME
> DECLARE @.OPERATOR SYSNAME
> CREATE TABLE #TEMP_TABLE (
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TABLE_ID INT,
> TABLE_NAME SYSNAME)
> CREATE TABLE ##RESULT(
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TYPE VARCHAR(15),
> TABLE_NAME VARCHAR(100),
> COLUMN_NAME VARCHAR(300),
> DATA_TEXT VARCHAR(7000) )
> INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
> SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
> BY NAME
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME = @.SEARCHSTR
> SET @.FROM = 1
> SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
> IF @.EXACT_MATCH = 'F'
> BEGIN
> IF @.MATCH_FULL_WORD = 'T'
> SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
> RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
> ELSE
> SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
> SELECT @.OPERATOR = ' LIKE '
> END
> ELSE
> SELECT @.OPERATOR = ' = '
> WHILE @.FROM <= @.TO
> BEGIN
> SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
> #TEMP_TABLE WHERE SLNO = @.FROM
> SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
> WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME = @.SEARCHSTR
> WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
> BY COLUMN_NAME)
> BEGIN
> SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
> #TEMP_COLUMNS ORDER BY COLUMN_NAME)
> EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
> DATA_TEXT)
> SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
> TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
> + ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
> + @.OPERATOR + '''' + @.SEARCHSTR + '''')
> DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
> END
> DROP TABLE #TEMP_COLUMNS
> SET @.FROM = @.FROM + 1
> END
> SELECT * FROM ##RESULT
> DROP TABLE #TEMP_TABLE
> DROP TABLE ##RESULT
> END
> GO
> -- EXEC FIND_IN_DB 'test%', 'F', 'T'
> ----
----
thanks I will try that

finding a string somewhere in the MS SQL user tables

Hi,
I have an application which shows a certain piece of data on the
screen, but I do not know which table it comes from (281 user tables).
How can I easily find which table has this data ?
(without opening each table and scanning through 1000's of rows
manually)
TIA
Michael
Use the profiler ... do the activity again to have a look at that
data ... In the backend it would definately show you the table or the
SP the application is calling ... Dig in further.
Regards
Bharat Butani.
On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael
|||If you now the column name you can do a search with object browser in query
analyzer on the database.
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"fauxDBA@.gmail.com" wrote:

> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
>
> Regards
> Bharat Butani.
>
> On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
>
>
|||On 4 May, 13:47, faux...@.gmail.com wrote:[vbcol=seagreen]
> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
> Regards
> Bharat Butani.
> On May 4, 3:39 pm,ChikenKoma<michaelnewp...@.yahoo.com> wrote:
>
>
thanks I will try that
|||On 4 May, 13:57, Hate_orphaned_users
<Hateorphanedus...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> If you now the column name you can do a search with object browser in query
> analyzer on the database.
> --
> I drank alot of beer and ended up in the police department database.
> Drank more beer and learned SQL in the dark hours.
> DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
> I love SQL
> "faux...@.gmail.com" wrote:
>
>
thats the problem we dont know the table, in Ingres I used to dump the
whole database to ascii files and do a string search, this would
highlight a file which corresponded to a table, then I could search
probable columns in that table.
|||The simplest way is the probably just to query the cataloguef or the tables,
processing the results to create select statement for each of the tables,
then run them until you find the column.
"Chiken Koma" <michaelnewport@.yahoo.com> wrote in message
news:1178275148.581808.186800@.l77g2000hsb.googlegr oups.com...
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael
>
|||If it just is the one time thing to search for a string, below is a SP
which I had written a long time back for fun (this is exactly similar
to what Mark had suggested). Use it rarely, and also not at the peak
time of the day.
------
DROP PROCEDURE FIND_IN_DB
GO
CREATE PROCEDURE FIND_IN_DB
@.SEARCHSTR VARCHAR(100),
@.EXACT_MATCH VARCHAR(1) = 'F',
@.MATCH_FULL_WORD VARCHAR(1) = 'F'
AS
BEGIN
DECLARE @.FROM INT
DECLARE @.TO INT
DECLARE @.TABLE_ID INT
DECLARE @.TABLE_NAME SYSNAME
DECLARE @.COLUMN_NAME SYSNAME
DECLARE @.OPERATOR SYSNAME
CREATE TABLE #TEMP_TABLE (
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TABLE_ID INT,
TABLE_NAME SYSNAME)
CREATE TABLE ##RESULT(
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TYPE VARCHAR(15),
TABLE_NAME VARCHAR(100),
COLUMN_NAME VARCHAR(300),
DATA_TEXT VARCHAR(7000) )
INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
BY NAME
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME = @.SEARCHSTR
SET @.FROM = 1
SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
IF @.EXACT_MATCH = 'F'
BEGIN
IF @.MATCH_FULL_WORD = 'T'
SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
ELSE
SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
SELECT @.OPERATOR = ' LIKE '
END
ELSE
SELECT @.OPERATOR = ' = '
WHILE @.FROM <= @.TO
BEGIN
SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
#TEMP_TABLE WHERE SLNO = @.FROM
SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME = @.SEARCHSTR
WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
BY COLUMN_NAME)
BEGIN
SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
#TEMP_COLUMNS ORDER BY COLUMN_NAME)
EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
DATA_TEXT)
SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
+ ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
+ @.OPERATOR + '''' + @.SEARCHSTR + '''')
DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
END
DROP TABLE #TEMP_COLUMNS
SET @.FROM = @.FROM + 1
END
SELECT * FROM ##RESULT
DROP TABLE #TEMP_TABLE
DROP TABLE ##RESULT
END
GO
-- EXEC FIND_IN_DB 'test%', 'F', 'T'
------
|||On 6 May, 12:57, nime...@.gmail.com wrote:
> If it just is the one time thing to search for a string, below is a SP
> which I had written a long time back for fun (this is exactly similar
> to what Mark had suggested). Use it rarely, and also not at the peak
> time of the day.
> ------
> DROP PROCEDURE FIND_IN_DB
> GO
> CREATE PROCEDURE FIND_IN_DB
> @.SEARCHSTR VARCHAR(100),
> @.EXACT_MATCH VARCHAR(1) = 'F',
> @.MATCH_FULL_WORD VARCHAR(1) = 'F'
> AS
> BEGIN
> DECLARE @.FROM INT
> DECLARE @.TO INT
> DECLARE @.TABLE_ID INT
> DECLARE @.TABLE_NAME SYSNAME
> DECLARE @.COLUMN_NAME SYSNAME
> DECLARE @.OPERATOR SYSNAME
> CREATE TABLE #TEMP_TABLE (
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TABLE_ID INT,
> TABLE_NAME SYSNAME)
> CREATE TABLE ##RESULT(
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TYPE VARCHAR(15),
> TABLE_NAME VARCHAR(100),
> COLUMN_NAME VARCHAR(300),
> DATA_TEXT VARCHAR(7000) )
> INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
> SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
> BY NAME
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME = @.SEARCHSTR
> SET @.FROM = 1
> SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
> IF @.EXACT_MATCH = 'F'
> BEGIN
> IF @.MATCH_FULL_WORD = 'T'
> SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
> RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
> ELSE
> SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
> SELECT @.OPERATOR = ' LIKE '
> END
> ELSE
> SELECT @.OPERATOR = ' = '
> WHILE @.FROM <= @.TO
> BEGIN
> SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
> #TEMP_TABLE WHERE SLNO = @.FROM
> SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
> WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME = @.SEARCHSTR
> WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
> BY COLUMN_NAME)
> BEGIN
> SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
> #TEMP_COLUMNS ORDER BY COLUMN_NAME)
> EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
> DATA_TEXT)
> SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
> TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
> + ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
> + @.OPERATOR + '''' + @.SEARCHSTR + '''')
> DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
> END
> DROP TABLE #TEMP_COLUMNS
> SET @.FROM = @.FROM + 1
> END
> SELECT * FROM ##RESULT
> DROP TABLE #TEMP_TABLE
> DROP TABLE ##RESULT
> END
> GO
> -- EXEC FIND_IN_DB 'test%', 'F', 'T'
> ------
thanks I will try that

finding a string somewhere in the MS SQL user tables

Hi,
I have an application which shows a certain piece of data on the
screen, but I do not know which table it comes from (281 user tables).
How can I easily find which table has this data ?
(without opening each table and scanning through 1000's of rows
manually)
TIA
MichaelUse the profiler ... do the activity again to have a look at that
data ... In the backend it would definately show you the table or the
SP the application is calling ... Dig in further.
Regards
Bharat Butani.
On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael|||If you now the column name you can do a search with object browser in query
analyzer on the database.
--
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL :)
"fauxDBA@.gmail.com" wrote:
> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
>
> Regards
> Bharat Butani.
>
> On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
> > Hi,
> >
> > I have an application which shows a certain piece of data on the
> > screen, but I do not know which table it comes from (281 user tables).
> >
> > How can I easily find which table has this data ?
> > (without opening each table and scanning through 1000's of rows
> > manually)
> >
> > TIA
> > Michael
>
>|||On 4 May, 13:47, faux...@.gmail.com wrote:
> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
> Regards
> Bharat Butani.
> On May 4, 3:39 pm,ChikenKoma<michaelnewp...@.yahoo.com> wrote:
> > Hi,
> > I have an application which shows a certain piece of data on the
> > screen, but I do not know which table it comes from (281 user tables).
> > How can I easily find which table has this data ?
> > (without opening each table and scanning through 1000's of rows
> > manually)
> > TIA
> > Michael
thanks I will try that|||On 4 May, 13:57, Hate_orphaned_users
<Hateorphanedus...@.discussions.microsoft.com> wrote:
> If you now the column name you can do a search with object browser in query
> analyzer on the database.
> --
> I drank alot of beer and ended up in the police department database.
> Drank more beer and learned SQL in the dark hours.
> DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
> I love SQL :)
> "faux...@.gmail.com" wrote:
> > Use the profiler ... do the activity again to have a look at that
> > data ... In the backend it would definately show you the table or the
> > SP the application is calling ... Dig in further.
> > Regards
> > Bharat Butani.
> > On May 4, 3:39 pm,ChikenKoma<michaelnewp...@.yahoo.com> wrote:
> > > Hi,
> > > I have an application which shows a certain piece of data on the
> > > screen, but I do not know which table it comes from (281 user tables).
> > > How can I easily find which table has this data ?
> > > (without opening each table and scanning through 1000's of rows
> > > manually)
> > > TIA
> > > Michael
thats the problem we dont know the table, in Ingres I used to dump the
whole database to ascii files and do a string search, this would
highlight a file which corresponded to a table, then I could search
probable columns in that table.|||The simplest way is the probably just to query the cataloguef or the tables,
processing the results to create select statement for each of the tables,
then run them until you find the column.
"Chiken Koma" <michaelnewport@.yahoo.com> wrote in message
news:1178275148.581808.186800@.l77g2000hsb.googlegroups.com...
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael
>|||If it just is the one time thing to search for a string, below is a SP
which I had written a long time back for fun (this is exactly similar
to what Mark had suggested). Use it rarely, and also not at the peak
time of the day.
------
DROP PROCEDURE FIND_IN_DB
GO
CREATE PROCEDURE FIND_IN_DB
@.SEARCHSTR VARCHAR(100),
@.EXACT_MATCH VARCHAR(1) = 'F',
@.MATCH_FULL_WORD VARCHAR(1) = 'F'
AS
BEGIN
DECLARE @.FROM INT
DECLARE @.TO INT
DECLARE @.TABLE_ID INT
DECLARE @.TABLE_NAME SYSNAME
DECLARE @.COLUMN_NAME SYSNAME
DECLARE @.OPERATOR SYSNAME
CREATE TABLE #TEMP_TABLE (
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TABLE_ID INT,
TABLE_NAME SYSNAME)
CREATE TABLE ##RESULT(
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TYPE VARCHAR(15),
TABLE_NAME VARCHAR(100),
COLUMN_NAME VARCHAR(300),
DATA_TEXT VARCHAR(7000) )
INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
BY NAME
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME = @.SEARCHSTR
SET @.FROM = 1
SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
IF @.EXACT_MATCH = 'F'
BEGIN
IF @.MATCH_FULL_WORD = 'T'
SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
ELSE
SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
SELECT @.OPERATOR = ' LIKE '
END
ELSE
SELECT @.OPERATOR = ' = '
WHILE @.FROM <= @.TO
BEGIN
SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
#TEMP_TABLE WHERE SLNO = @.FROM
SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME = @.SEARCHSTR
WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
BY COLUMN_NAME)
BEGIN
SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
#TEMP_COLUMNS ORDER BY COLUMN_NAME)
EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
DATA_TEXT)
SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
+ ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
+ @.OPERATOR + '''' + @.SEARCHSTR + '''')
DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
END
DROP TABLE #TEMP_COLUMNS
SET @.FROM = @.FROM + 1
END
SELECT * FROM ##RESULT
DROP TABLE #TEMP_TABLE
DROP TABLE ##RESULT
END
GO
-- EXEC FIND_IN_DB 'test%', 'F', 'T'
------|||On 6 May, 12:57, nime...@.gmail.com wrote:
> If it just is the one time thing to search for a string, below is a SP
> which I had written a long time back for fun (this is exactly similar
> to what Mark had suggested). Use it rarely, and also not at the peak
> time of the day.
> ------
> DROP PROCEDURE FIND_IN_DB
> GO
> CREATE PROCEDURE FIND_IN_DB
> @.SEARCHSTR VARCHAR(100),
> @.EXACT_MATCH VARCHAR(1) = 'F',
> @.MATCH_FULL_WORD VARCHAR(1) = 'F'
> AS
> BEGIN
> DECLARE @.FROM INT
> DECLARE @.TO INT
> DECLARE @.TABLE_ID INT
> DECLARE @.TABLE_NAME SYSNAME
> DECLARE @.COLUMN_NAME SYSNAME
> DECLARE @.OPERATOR SYSNAME
> CREATE TABLE #TEMP_TABLE (
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TABLE_ID INT,
> TABLE_NAME SYSNAME)
> CREATE TABLE ##RESULT(
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TYPE VARCHAR(15),
> TABLE_NAME VARCHAR(100),
> COLUMN_NAME VARCHAR(300),
> DATA_TEXT VARCHAR(7000) )
> INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
> SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
> BY NAME
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME = @.SEARCHSTR
> SET @.FROM = 1
> SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
> IF @.EXACT_MATCH = 'F'
> BEGIN
> IF @.MATCH_FULL_WORD = 'T'
> SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
> RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
> ELSE
> SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
> SELECT @.OPERATOR = ' LIKE '
> END
> ELSE
> SELECT @.OPERATOR = ' = '
> WHILE @.FROM <= @.TO
> BEGIN
> SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
> #TEMP_TABLE WHERE SLNO = @.FROM
> SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
> WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME = @.SEARCHSTR
> WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
> BY COLUMN_NAME)
> BEGIN
> SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
> #TEMP_COLUMNS ORDER BY COLUMN_NAME)
> EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
> DATA_TEXT)
> SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
> TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
> + ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
> + @.OPERATOR + '''' + @.SEARCHSTR + '''')
> DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
> END
> DROP TABLE #TEMP_COLUMNS
> SET @.FROM = @.FROM + 1
> END
> SELECT * FROM ##RESULT
> DROP TABLE #TEMP_TABLE
> DROP TABLE ##RESULT
> END
> GO
> -- EXEC FIND_IN_DB 'test%', 'F', 'T'
> ------
thanks I will try that

Finding a column in a database

Hi all,

How do I find all tables containing a column (say a column including
the string 'value')?
Thanks

BrunoSELECT sysCol.Name, sysType.name
FROM syscolumns sysCol
INNER JOIN sysobjects sysObj ON sysCol.id = sysObj.id
INNER JOIN systypes sysType on sysCol.xtype = sysType.xtype
WHERE sysObj.name ='<TABLE NAME>'

best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
------------

*** Sent via Developersdex http://www.developersdex.com ***|||If you mean a column with 'value' in the column name (not in the data),
then there are a couple of ways:

select object_name(id), name
from syscolumns
where name like '%value%'

Or if you want a portable solution, you can use the INFORMATION_SCHEMA
views:

select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME like '%value%'

Books Online has more information about syscolumns and the views.

Simon

find? instr? indexof?

is there a sql keyword for find or instr?
i have a field i wish to make into two and i need the position of a string "-" in the field so i can do a select right and copy that data to a new colmCHARINDEX
Returns the starting position of the specified expression in a character string.

Syntax
CHARINDEX ( expression1 , expression2 [ , start_location ] )sql

find text string in database

Hello,
I'd like to find a specific text string searching in all tables within same database.
Is there a way to make only one query to all tables at the same time?
Thank you very much for your attention.
QslxNo. You would have to write a procedure that looped through all the tables and checked every column.

This question makes me suspect that there are some design issues with your database schema.

blindman|||Ya mean like:

USE Northwind
GO

CREATE TABLE myTable99 (TABLE_NAME sysname, COLUMN_NAME sysname, Occurs int)
GO

SET NOCOUNT ON

DECLARE @.SQL varchar(8000), @.TABLE_NAME sysname, @.COLUMN_NAME sysname, @.Sargable varchar(80), @.Count int

SELECT @.Sargable = 'Beer'

DECLARE insaneCursor CURSOR FOR
SELECT c.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns c INNER JOIN INFORMATION_SCHEMA.Tables t
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
WHERE c.DATA_TYPE IN ('char','nchar','varchar','nvarchar','text','ntext ')
AND t.TABLE_TYPE = 'BASE TABLE'

OPEN insaneCursor

FETCH NEXT FROM insaneCursor INTO @.TABLE_NAME, @.COLUMN_NAME

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.SQL = 'INSERT INTO myTable99 (TABLE_NAME, COLUMN_NAME, Occurs) SELECT '
+ '''' + @.TABLE_NAME + '''' + ','
+ '''' + @.COLUMN_NAME + '''' + ','
+ 'COUNT(*) FROM [' + @.TABLE_NAME
+ '] WHERE [' + @.COLUMN_NAME + '] Like '
+ ''''+ '%' + @.Sargable + '%' + ''''
--SELECT @.SQL
EXEC(@.SQL)
IF @.@.ERROR <> 0
BEGIN
SELECT @.SQL
SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @.TABLE_NAME
GOTO Error
END
FETCH NEXT FROM insaneCursor INTO @.TABLE_NAME, @.COLUMN_NAME
END

SELECT * FROM myTable99 WHERE Occurs <> 0

Error:
CLOSE insaneCursor
DEALLOCATE insaneCursor

GO

DROP TABLE myTable99
GO

SET NOCOUNT OFF|||Hi Brett,
Thanks a lot for you help.
It works great!

Cheers,|||brett, don't you have any hobbies? :p|||Yeah...SQL

That was a cut and paste from my toolbox...

You kidding...I'm writting a sql server version of the window explorer find function...using xp_cmdshell, because it's tooooooooooooo painful to deal with server ops...|||I can't tell when you're kidding and when you're not!|||I'd say I'm an Enigma...but that's taken already...8-)

And I finally got server ops to give me clearence, so I won't have to build the explorer

Are Margarittas a hobby?

Find text in collapsed drilldown report

I have a drilldown report. Without opening a drilldown item, is there a way
to search for a text string using the "Find" link when viewing a report? If
not, what would be the best recommendation to accomplish this.Hello Parker,
Without opening the drilldown item, you could not use the "Find" button to
find the text in the drilldown item.
This is by design because the find operation only search the HTML which is
appearanced.
My suggestion is that add a parameter in the report to control to expanded
all the items.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Find string in Stored Procedures (sp_executesql)

Trying to find a view that is called from many stored procedures - need name
of view only. Query below does not work. Do not want to execute?
declare @.tblname varchar(120),@.sql nvarchar(4000),@.gettext varchar(8000)
declare mycur cursor for
select name from sysobjects where xtype='P'
open mycur
fetch next from mycur into @.tblname
while @.@.fetch_status=0
begin
select @.sql=N'exec sp_helptext '+@.tblname
EXEC sp_executesql
@.stmt = @.sql,
@.params = N'@.gettext int output',
@.gettext= @.gettext output
if charindex( 'ATVINVSTANDARDVIEW',upper(@.gettext))>0
print @.tblname
fetch next from mycur into @.tblname
end
close mycur
deallocate mycur
Regards,
Jamie
You may want to check out the system table syscomments where the source of
all user procedures are held.
Anith
|||http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:96C2F1E4-2672-4DCF-BA4B-EAB8227F44CD@.microsoft.com...
> Trying to find a view that is called from many stored procedures - need
> name
> of view only. Query below does not work. Do not want to execute?
> declare @.tblname varchar(120),@.sql nvarchar(4000),@.gettext varchar(8000)
> declare mycur cursor for
> select name from sysobjects where xtype='P'
> open mycur
> fetch next from mycur into @.tblname
> while @.@.fetch_status=0
> begin
> select @.sql=N'exec sp_helptext '+@.tblname
> EXEC sp_executesql
> @.stmt = @.sql,
> @.params = N'@.gettext int output',
> @.gettext= @.gettext output
> if charindex( 'ATVINVSTANDARDVIEW',upper(@.gettext))>0
> print @.tblname
> fetch next from mycur into @.tblname
> end
> close mycur
> deallocate mycur
>
> --
> Regards,
> Jamie

Find string in Stored Procedures (sp_executesql)

Trying to find a view that is called from many stored procedures - need name
of view only. Query below does not work. Do not want to execute?
---
declare @.tblname varchar(120),@.sql nvarchar(4000),@.gettext varchar(8000)
declare mycur cursor for
select name from sysobjects where xtype='P'
open mycur
fetch next from mycur into @.tblname
while @.@.fetch_status=0
begin
select @.sql=N'exec sp_helptext '+@.tblname
EXEC sp_executesql
@.stmt = @.sql,
@.params = N'@.gettext int output',
@.gettext= @.gettext output
if charindex( 'ATVINVSTANDARDVIEW',upper(@.gettext))>0
print @.tblname
fetch next from mycur into @.tblname
end
close mycur
deallocate mycur
Regards,
JamieYou may want to check out the system table syscomments where the source of
all user procedures are held.
Anith|||http://databases.aspfaq.com/databas...br />
ext.html
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:96C2F1E4-2672-4DCF-BA4B-EAB8227F44CD@.microsoft.com...
> Trying to find a view that is called from many stored procedures - need
> name
> of view only. Query below does not work. Do not want to execute?
> ---
> declare @.tblname varchar(120),@.sql nvarchar(4000),@.gettext varchar(8000)
> declare mycur cursor for
> select name from sysobjects where xtype='P'
> open mycur
> fetch next from mycur into @.tblname
> while @.@.fetch_status=0
> begin
> select @.sql=N'exec sp_helptext '+@.tblname
> EXEC sp_executesql
> @.stmt = @.sql,
> @.params = N'@.gettext int output',
> @.gettext= @.gettext output
> if charindex( 'ATVINVSTANDARDVIEW',upper(@.gettext))>0
> print @.tblname
> fetch next from mycur into @.tblname
> end
> close mycur
> deallocate mycur
>
> --
> Regards,
> Jamie

Find string between 2 characters and insert in different column

I have data in a char column like this:
[1]
[2]
[3]
etc
[100]
[101]
etc
[1000]
etc
I want to select everything between the brackets and insert into a different
column.
Any help please.How about
SELECT REPLACE(REPLACE(YourCol,'[',''),']','')
FROM YourTable
Andrew J. Kelly SQL MVP
"Terri" <terri@.cybernets.com> wrote in message
news:d4p3vr$l0e$1@.reader2.nmix.net...
>I have data in a char column like this:
> [1]
> [2]
> [3]
> etc
> [100]
> [101]
> etc
> [1000]
> etc
> I want to select everything between the brackets and insert into a
> different
> column.
> Any help please.
>
>|||UPDATE Floob
SET new_foo =
REPLACE ( REPLACE (foobar, '[', ''), ']' '');
or you can do this in a VIEW or computed column.|||As a general approach, you can use the following expression:
SUBSTRING( @.s, CHARINDEX( '[', @.s ) + 1,
CHARINDEX( ']', @.s,
CHARINDEX( '[', @.s ) ) -
CHARINDEX( '[', @.s ) - 1 )
Anith|||Hi Terri,
Try this query...
SELECT Substring(YouCol,2,len(youCol)-2) FROM YourTable
Swami.
"Terri" wrote:

> I have data in a char column like this:
> [1]
> [2]
> [3]
> etc
> [100]
> [101]
> etc
> [1000]
> etc
> I want to select everything between the brackets and insert into a differe
nt
> column.
> Any help please.
>
>

find string

I want to find if a data string is somewhere in a table but i dont
know the table of the database or the field. Who can i search
anywhere?

Thanks

Brainjkhttp://vyaskn.tripod.com/search_all..._all_tables.htm

--
David Portas
----
Please reply only to the newsgroup
--

Find specific text in a string

Hai ,
in a textbox am having text as
"(20/100)+pay1*pay2" .it's a formula. and stored in a particular
variable.
string strformula="(20/100)+pay1*pay2" ;
i've to substitute the value of the variable 'pay1' & 'pay2' and
finding the value of that strformula.
can any onr tell me how to find 'pay1' and 'pay2' in the variable
strformula. it's urgent and reply immediately.
Thanks in advance.Hi
I am not sure what this has to do with SQL Server!
If the strings are unique then you could use the replace function.
John
<ksrajalakshmi@.gmail.com> wrote in message
news:1139642884.262422.60580@.g14g2000cwa.googlegroups.com...
> Hai ,
> in a textbox am having text as
> "(20/100)+pay1*pay2" .it's a formula. and stored in a particular
> variable.
> string strformula="(20/100)+pay1*pay2" ;
> i've to substitute the value of the variable 'pay1' & 'pay2' and
> finding the value of that strformula.
> can any onr tell me how to find 'pay1' and 'pay2' in the variable
> strformula. it's urgent and reply immediately.
> Thanks in advance.
>|||If am having the value as
string strvalue ="(12.23+233.56)*12/100";
i've to find the value.so that am converting to double. but it throws
error. tel me how to find value?|||Hi
It is still not clear if you are using SQL Server! If you are then you can
do something like:
DECLARE @.strformula nvarchar(60)
DECLARE @.nparams nvarchar(80)
DECLARE @.output decimal(10,4)
DECLARE @.pay1 decimal(10,4)
DECLARE @.pay2 decimal(10,4)
SET @.strformula = N'SELECT @.output_val = (20.0/100)+(@.pay_1*@.pay_2)'
SET @.nparams = N'@.output_val decimal(10,4) OUTPUT, @.pay_1 decimal(10,4),
@.pay_2 decimal(10,4)'
SET @.pay1 = 233.56
SET @.pay2 = 12.0/100
SELECT @.strformula
SELECT @.nparams
EXEC sp_executesql @.strformula, @.nparams, @.output_val = @.output OUTPUT,
@.pay_1 = @.pay1, @.pay_2 = @.pay2
SELECT @.output
John
<ksrajalakshmi@.gmail.com> wrote in message
news:1139650655.809064.217710@.f14g2000cwb.googlegroups.com...
> If am having the value as
> string strvalue ="(12.23+233.56)*12/100";
> i've to find the value.so that am converting to double. but it throws
> error. tel me how to find value?
>|||Actually I meant that your syntax wasn't SQL syntax:

Not mine. It is T-SQL :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message news:...
> First, this clearly isn't SQL Syntax, but that notwithstanding, you can
> evaluate a function like this, (as long as it fits SQL Syntax of course)
> declare @.value decimal (10,8)
> declare @.formula varchar(200), @.query nvarchar(2000)
> set @.formula = '(12.23+233.56)*12/100'
> set @.query = 'select @.value = (' + @.formula + ')'
> EXEC sp_executesql @.query,
> N'@.Value decimal(10,8) output',
> @.value output
> select @.value
> I would strongly suggest against it, since this is really not SQL's
> strongpoint. This is one of the rare cases where I would probably suggest
> you storing the expression and the answer in two columns (using the middle
> tier layer to calculate the value, or this method could be used in a a
> singleton insert.)
>
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing."
> (Oscar Wilde)
> <ksrajalakshmi@.gmail.com> wrote in message
> news:1139650655.809064.217710@.f14g2000cwb.googlegroups.com...
>|||First, this clearly isn't SQL Syntax, but that notwithstanding, you can
evaluate a function like this, (as long as it fits SQL Syntax of course)
declare @.value decimal (10,8)
declare @.formula varchar(200), @.query nvarchar(2000)
set @.formula = '(12.23+233.56)*12/100'
set @.query = 'select @.value = (' + @.formula + ')'
EXEC sp_executesql @.query,
N'@.Value decimal(10,8) output',
@.value output
select @.value
I would strongly suggest against it, since this is really not SQL's
strongpoint. This is one of the rare cases where I would probably suggest
you storing the expression and the answer in two columns (using the middle
tier layer to calculate the value, or this method could be used in a a
singleton insert.)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
<ksrajalakshmi@.gmail.com> wrote in message
news:1139650655.809064.217710@.f14g2000cwb.googlegroups.com...
> If am having the value as
> string strvalue ="(12.23+233.56)*12/100";
> i've to find the value.so that am converting to double. but it throws
> error. tel me how to find value?
>

find space (MS SQL 2005)

how can I find a space into a string ? (for MS SQL 2005)
SELECT name FROM tbNames WHERE name LIKE '% % '
is not working
thank youwhat do you mean it's not working? it should

can you give an example of a name with a space in it where it doesn't work?|||I dont understand ... now it works

thanks r937|||sounds like a heisenbug|||Usually when that happens to me it is caused by an id-10-t error.

-PatP|||... heisenbugthank you so much, what a gorgeous word, that's brilliant

:)

pat, see A Truly ID-iotic Design (http://worsethanfailure.com/Articles/A_Truly_ID-iotic_Design.aspx)

Edit: oh hai, it r down atm, plz try again later, kthxbye|||do you mean it can happen time to time with no reason ?|||it's not my creation. there are several related ones:

http://en.wikipedia.org/wiki/Heisenbug

Find second character in a string

Hi,
I have a string such as .75.34.100.
How do I find the position of the second comma from the right.
I been trying to use PATINDEX
DECLARE @.string varchar(30)
SET @.string = '.75.34.100.'
SELECT PATINDEX('%.', LEFT(@.string, LEN(@.string)-1))
but it gives me 0
any insight on this?
Thanks in advance
Christian"Christian Perthen" <abracadabara@.dontreplytothidress.com> wrote in
message news:ej3EVBcmFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a string such as .75.34.100.
> How do I find the position of the second comma from the right.
> I been trying to use PATINDEX
> DECLARE @.string varchar(30)
> SET @.string = '.75.34.100.'
> SELECT PATINDEX('%.', LEFT(@.string, LEN(@.string)-1))
> but it gives me 0
> any insight on this?
> Thanks in advance
> Christian
>
Take a look at the REVERSE function. This will reverse your character
string. Then you can use your PATINDEX or CHARINDEX.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks Rick,
but if I do a reverse then I need to find the position of the second comma
from the left instead of right.
So I will still be stuck in the same situation. Note, all my strings starts
and end with a comma.
--Americas .75.86.
-- Puerto Rico .75.86.17.
-- Latin America .75.86.70.
-- Dominican Republic
.75.86.70.108.
-- Haiti
.75.86.70.110.
-- South America
.75.86.70.22.
-- Uruguay
.75.86.70.22.125.
-- Peru
.75.86.70.22.19.
-- Argentina
.75.86.70.22.21.
-- Brazil
.75.86.70.22.53.
-- Chile
.75.86.70.22.69.
what I need to do is be able to sort by country name and I can do that by
getting rid of the last number in the path.
Thanks
Christian
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OWT6JGcmFHA.3608@.TK2MSFTNGP15.phx.gbl...
> "Christian Perthen" <abracadabara@.dontreplytothidress.com> wrote in
> message news:ej3EVBcmFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Take a look at the REVERSE function. This will reverse your character
> string. Then you can use your PATINDEX or CHARINDEX.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||"Christian Perthen" <abracadabara@.dontreplytothidress.com> wrote in
message news:%23rf13QcmFHA.2152@.TK2MSFTNGP14.phx.gbl...
> Thanks Rick,
> but if I do a reverse then I need to find the position of the second comma
> from the left instead of right.
> So I will still be stuck in the same situation. Note, all my strings
> starts
> and end with a comma.
> --Americas .75.86.
> -- Puerto Rico .75.86.17.
> -- Latin America .75.86.70.
> -- Dominican Republic
> .75.86.70.108.
> -- Haiti
> .75.86.70.110.
> -- South America
> .75.86.70.22.
> -- Uruguay
> .75.86.70.22.125.
> -- Peru
> .75.86.70.22.19.
> -- Argentina
> .75.86.70.22.21.
> -- Brazil
> .75.86.70.22.53.
> -- Chile
> .75.86.70.22.69.
> what I need to do is be able to sort by country name and I can do that by
> getting rid of the last number in the path.
> Thanks
> Christian
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:OWT6JGcmFHA.3608@.TK2MSFTNGP15.phx.gbl...
>
Try this out...
CREATE TABLE #Foo (
Value varchar(20)
)
INSERT #Foo VALUES ('100,28,390,20,14,')
INSERT #Foo VALUES ('10,13,390,71,14,')
INSERT #Foo VALUES ('100,28,390,12,15424,')
INSERT #Foo VALUES ('100,28,390,33,26,')
INSERT #Foo VALUES ('100,28,39080,14,')
SELECT LEFT(Value, LEN(Value) - -- Get the LEFT of the Length minus
the CHARINDEX value of the second comma
CHARINDEX(',', REVERSE(Value), -- Find Second comma from the right
(CHARINDEX(',', REVERSE(Value), 1) + 1))) -- Find First comma from the
right
FROM #Foo
DROP TABLE #Foo
Rick Sawtell
MCT, MCSD, MCDBA|||> but if I do a reverse then I need to find the position of the second comma
> from the left instead of right.
Maybe you could use fn_split() if the number of decimal points (not commas)
is constant.
http://msdn.microsoft.com/library/e...eatYourself.asp|||Try using RIGHT instead of LEFT.
SELECT PATINDEX('%.', RIGHT(@.string, LEN(@.string)-1))
Hope this helps.
TDN
"Christian Perthen" <abracadabara@.dontreplytothidress.com> wrote in
message news:ej3EVBcmFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a string such as .75.34.100.
> How do I find the position of the second comma from the right.
> I been trying to use PATINDEX
> DECLARE @.string varchar(30)
> SET @.string = '.75.34.100.'
> SELECT PATINDEX('%.', LEFT(@.string, LEN(@.string)-1))
> but it gives me 0
> any insight on this?
> Thanks in advance
> Christian
>|||You can use this (adapting from another post)
e.g.
select top 8000 digit=identity(int,1,1)
into digits
from sysobjects,syscolumns
go
create function dbo.xtract(@.input varchar(8000))
returns varchar(8000)
as
begin
declare @.tb table (i int identity primary key, value sysname)
declare @.s varchar(8000)
declare @.delim char
-- desired delimiter
set @.delim=','
set @.input = @.delim+rtrim(ltrim(@.input))+@.delim
insert @.tb
select substring(@.input, n.digit+1,
charindex(@.delim,@.input,n.digit+1)-n.digit-1) value
from digits as n
where n.digit<len(@.input)
and substring(@.input,n.digit,1) = @.delim
-- pick the desired value
select @.s=value
from @.tb
where i=2
return @.s
end
go
declare @.csv varchar(8000)
set @.csv='asdga,1324,afasf afds'
select dbo.xtract(@.csv)
-oj
"Christian Perthen" <abracadabara@.dontreplytothidress.com> wrote in
message news:%23rf13QcmFHA.2152@.TK2MSFTNGP14.phx.gbl...
> Thanks Rick,
> but if I do a reverse then I need to find the position of the second comma
> from the left instead of right.
> So I will still be stuck in the same situation. Note, all my strings
> starts
> and end with a comma.
> --Americas .75.86.
> -- Puerto Rico .75.86.17.
> -- Latin America .75.86.70.
> -- Dominican Republic
> .75.86.70.108.
> -- Haiti
> .75.86.70.110.
> -- South America
> .75.86.70.22.
> -- Uruguay
> .75.86.70.22.125.
> -- Peru
> .75.86.70.22.19.
> -- Argentina
> .75.86.70.22.21.
> -- Brazil
> .75.86.70.22.53.
> -- Chile
> .75.86.70.22.69.
> what I need to do is be able to sort by country name and I can do that by
> getting rid of the last number in the path.
> Thanks
> Christian
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:OWT6JGcmFHA.3608@.TK2MSFTNGP15.phx.gbl...
>|||Try:
SELECT
SUBSTRING( address, LEN( address ) - CHARINDEX( '.', REVERSE( address ),
CHARINDEX( '.', REVERSE( address ), 2 ) + 1 ) + 2, 10 )
FROM <your_table>
Replace the 'address' string with your field and put in your table for
<your_table>
Let me know how you get on.
Damien
"Christian Perthen" wrote:

> Thanks Rick,
> but if I do a reverse then I need to find the position of the second comma
> from the left instead of right.
> So I will still be stuck in the same situation. Note, all my strings start
s
> and end with a comma.
> --Americas .75.86.
> -- Puerto Rico .75.86.17.
> -- Latin America .75.86.70.
> -- Dominican Republic
> ..75.86.70.108.
> -- Haiti
> ..75.86.70.110.
> -- South America
> ..75.86.70.22.
> -- Uruguay
> ..75.86.70.22.125.
> -- Peru
> ..75.86.70.22.19.
> -- Argentina
> ..75.86.70.22.21.
> -- Brazil
> ..75.86.70.22.53.
> -- Chile
> ..75.86.70.22.69.
> what I need to do is be able to sort by country name and I can do that by
> getting rid of the last number in the path.
> Thanks
> Christian
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:OWT6JGcmFHA.3608@.TK2MSFTNGP15.phx.gbl...
>
>|||Hi There,
You may try this. Little restrictive but works.
declare @.v varchar(80)
set @.v='a,jat,pat,kat'
set @.v = replace (@.v,',','.')
select charindex('.'+parsename(@.v,2),@.v)
With warm regards
Jatinder Singh